Countif with criteria from multiple columns

[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

 

[Bob Phillips]

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)

 

[Ken Wright]

You cannot use whole range references in array formulas (This includes
SUMPRODUCT). Reduce the range or use a dynamic one.

 

[denisel]

So the sum product works if I use a defined range as opposed to th
whole column - Thanks so much for everyone's help!!

 

[Ken Wright]

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)-1))*(OFFSET($F$1,1,,COUNTA(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.