Countif with multiple criteria

[bradsalmon]

Hi all,

I'm looking to try and include more than one criteria in a countif
statement. I've seen the examples of adding to separate countif
statements together but what I want is something where both conditions
are true not just a count of each.

I'll try to explain with an example.

Fruit Type Eaten
Apple Red No
Apple Red Yes
Apple Green Yes
Apple Green Yes
Apple Yellow No
Peach Large Yes
Peach Small No

So how many Green apples have been eaten? Hence, I'm trying to count
apples, then the green ones within that and then the green ones that
have been eaten.

Any pointers very gratefully accepted.

Thanks,

Brad

 

[Dave Peterson]

=sumproduct(--(a1:a99="Apple"),--(b1:b99="green"),--(c1:c99="eaten"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


And if you're using xl2007, you may want to look at =countifs() in excel's help.

 

[Bob Phillips]

=SUMPRODUCT(--(A2:A20="Apple"),--(B2:B20="Green"),--(C2:C20="Yes"))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[bradsalmon]

Thanks all. I'm using Excel 2003 SP3

Couldn't get this working at first but with the helpful links that
Dave provided figured out that
=sumproduct((a1:a99="Apple")*(b1:b99="Green")*(c1:c99="Yes")) does
what I need it to.

I haven't a clue how this works though, which kinda bugs me.

Brad

 

[Pete_UK]

Bob explains it all here:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps.

Pete

 

[Dave Peterson]

Oops. I screwed up my formula.

This:
=sumproduct(--(a1:a99="Apple"),--(b1:b99="green"),--(c1:c99="eaten"))
should have been:
=sumproduct(--(a1:a99="Apple"),--(b1:b99="green"),--(c1:c99="yes"))