Countif with criteria from multiple columns

  • Thread starter Thread starter denisel
  • Start date Start date
D

denisel

Hello - I wrote about this on Saturday and have tried both the
sumproduct and sum array formulas and neither of them work.

Here are my two formulas - Can anyone tell me what I am doing wrong? I
am at a total loss. I thought it might be because there are blank
cells in the columns but even when I fill them in I get an error
message.

=SUMPRODUCT(('GM Approval Spreadsheet'!E:E="OAT Americas")*('GM
Approval Spreadsheet'!K:K="Yes"))

{=SUM(IF(('GM Approval Spreadsheet'!$E:$E="OAT Americas")*('GM Approval
Spreadsheet'!$K:$K="Yes"),1,0))}

Both of these formulas return the #NUM! error.

Thanks,
Denise
 
Denise,

SUMPRODUCT does not work on a whole column, you have to specify a start and
end row

=SUMPRODUCT(('GM Approval Spreadsheet'!E1:E1000="OAT Americas")*('GM
Approval Spreadsheet'!K1:K1000="Yes"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
You cannot use whole range references in array formulas (This includes
SUMPRODUCT). Reduce the range or use a dynamic one.
 
So the sum product works if I use a defined range as opposed to th
whole column - Thanks so much for everyone's help!!
 
Correct. One thing you might want to consider is using the OFFSET function to
create a dynamic array, eg assuming you had no blanks in the data in say Col D,
but had headings in row 1, then you might use something like the following:-

=SUMPRODUCT((OFFSET($D$1,1,,COUNTA(D:D)-1))*(OFFSET($F$1,1,,COUNTA(D:D)-1)))

Note the COUNTA refers to the same range in each part of the formula, even
though theinitial offset starts in a different cell. This ensures that the
ranges remain consistent in length.
 
Back
Top