Count items in column that match a specific cell, only if another cell is 0?

[mcalice]

How can I count all matching items in a column that match a specific cell, but only if a third cell is equal to 0?

I need to count everything in column A that matches cell D2, but ONLY if the same row in column B=0.

column B is a Countif formula if that makes any difference.


This probably goes without saying, but I'm a newbie to Excel formulas! But I've been gaining significant ground over the last few weeks thanks to this awesome site and forum!

thanks in advance for any help!!

 

[Hui]

=COUNTIFS(A3:A15,D2,B3:B15,0)

adjust ranges to suit


or


=COUNTIFS(A:A,D2,B:B,0)

 

[vasim]

=SUMPRODUCT(($A$1:$A$100=D1)*($B$1:$B$100=0))


If Excel 2003


Credits: http://www.mrexcel.com/forum/showthread.php?t=50463

 

[mcalice]

Wow, thank you both SO much - they both work GREAT!

Is there any advantage to using COUNTIFS versus SUMPRODUCT or vice versa?

 

[vasim]

Countifs are no supported by Excel 2003. Thats it

 

[mcalice]

ok, thanks!

I'm using Excel for Mac 2011 and both work beautifully!

thanks again to both of you for your quick responses!!

 

[mcalice]

Is it possible to add another condition to this?

I need to only count the number of items in column a whose date, in column E, is on or after a static date entered in cell AF3.


I'm sure there is probably a very obvious solution, but my brain is completely muddled with the various searches I've tried so I'm at a loss to try to figure this out!


thanks for any and all help!

 

[Luke M]

=SUMPRODUCT(($A$1:$A$100=D1)*($B$1:$B$100=0)*($E$1:$E$100>=AF3))

 

[mcalice]

See, I KNEW it would be simple! Thank you so much!

I obviously have a problem with "over-thinking" these things!

Funny how hard it is to grasp logic sometimes!

thanks again, you're a life saver!