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

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

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

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

Regards,
Ryan---

--
RyGuy


"Jonathan" wrote:

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

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

But that doesn't factor in the ID#

"ryguy7272" wrote:

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

Regards,
Ryan---

--
RyGuy


"Jonathan" wrote:

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

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

Try this:

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

--
Biff
Microsoft Excel MVP


"Jonathan" wrote in message
...
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

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

Thanks--what if the ID# includes letters?

"T. Valko" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Jonathan" wrote in message
...
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

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

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


--
Biff
Microsoft Excel MVP


"Jonathan" wrote in message
...
Thanks--what if the ID# includes letters?

"T. Valko" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Jonathan" wrote in message
...
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