Multiple Criteria, Count If, Sum Product to get count across range

[Jonathan]

Hi all,

Pseudo-Follow-Up to my last question:

Going based on the below example, how can I write a formula that will give
me a count of the total lines that meet this criteria: Name--Dog, Male--Y,
Brown--Y, Size--Big, ID # only 1, 2, 3, 5, 7, 8, 15, 16. So, in the case
below, the count/value showing up in the cell with the formula I want would
be (if I can count correctly) 2. Eliminating the last field, I would've used
SumProduct for all of these, but now that I'm breaking it down further to
include specific ID numbers, I'm not sure what the best and efficient way I
can do it is. I'm sure I could do
SumProduct(Name)*(Male)*(Brown)*Size*(ID#_) + SumProduct...*ID#_ but that
would just be a large hassle and I wondered if there's a smaller formula.
Thanks!

Name Male Brown Size ID#
Dog Y Y Big 1
Dog N Y Big 2
Dog Y Y Small 3
Cat Y Y Small 4
Cat Y Y Big 5
Dog Y N Big 6
Dog Y Y Big 7
Cat Y Y Big 8
Cat Y Y Medium 9
Apple N Y Small 10
Banana N Y Small 11
Apple N Y Small 12
Apple N Y Big 13
Apple N Y Big 14
Dog N N Big 15
Dog Y N Small 16
Dog Y Y Big 17
Banana N N Medium 18

 

[ryguy7272]

This yields a result of 3:
=SUMPRODUCT(--(A1:A19="Dog"),--(B1:B19="Y"),--(C1:C19="Y"),--(D119="Big"))

Regards,
Ryan---

 

[Jonathan]

But that doesn't factor in the ID#

This yields a result of 3:
=SUMPRODUCT(--(A1:A19="Dog"),--(B1:B19="Y"),--(C1:C19="Y"),--(D119="Big"))

Regards,
Ryan---

 

[T. Valko]

Try this:

=SUMPRODUCT(--(name="dog"),--(male="y"),--(brown="y"),--(size="big"),--(ISNUMBER(MATCH(ID,{1,2,3,5,7,8,15,16},0))))

 

[Jonathan]

Thanks--what if the ID# includes letters?

Try this:

=SUMPRODUCT(--(name="dog"),--(male="y"),--(brown="y"),--(size="big"),--(ISNUMBER(MATCH(ID,{1,2,3,5,7,8,15,16},0))))

 

[T. Valko]

Doesn't make a difference except in the way you enter them in the MATCH
function:

=SUMPRODUCT(--(name="dog"),--(male="y"),--(brown="y"),--(size="big"),--(ISNUMBER(MATCH(ID,{"A21E","B","200F","3H",10},0))))

If the ID contains letters then those are TEXT values. Text values need to
be enclosed in double quote: "A21E". Numeric values do not need to be
enclosed in quotes: 10