Using COUNTIFS with 2 criteria on 2 different sized ranges?

3
votes

For my current research I want to return a count of a value (A, B or C) listed in column AO, when a criteria in a differently sized range (T:Y) is met. The criteria is that it simply has to be a specific word (lets say "Jack", "Tony" and "Bobby")

Example:

enter image description here

I would for instance like to know all the A, B & C's for all the Jacks (in this case 2, 0, 1) but i can't seem to use the countifs formula as it requires the ranges of both criteria to be the same?

1
excelexcel-formulaexcel-2010

1 Answers

4
votes

That would be a SumProduct formula:

=SUMPRODUCT(--(A2:C5="Jack")*--(D2:D5="A"))