Countif with multiple criteria

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

=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 he
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.

bradsalmon wrote:

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

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

Bob explains it all he

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

Hope this helps.

Pete

On May 2, 2:56*pm, bradsalmon wrote:
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

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"))



bradsalmon wrote:

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

--

Dave Peterson