Hi All...

I know somewhere I have done this before, but for some reason I am not getting the correct result.

I have a set of data, there are basically 2 columns to focus on. Time & Name.
I need to do a countif on the values that are greater than or equal to 0 if the name of a column equals a specific criteria.

E.G

A B
1 Dog 1
2 Dog -1
3 Cat 5
4 Dog 2
5 Cat -7
6 Dog 0


So when doing the calculation for dog being greater than or equal to 0 I should get 3.

I used =IF(A1:A6="Dog",(COUNTIF(B1:B6,"=0"))). This however gives me the incorrect results. When I do the calculation as show I get 4 instead of 3. It is counting all the values that are greater than or equal to 0 and not only the ones that have the word Dog in Column A.

Either of the following will do the trick.

=SUMPRODUCT((A1:A6="Dog")*(B1:B6=0))

=COUNTIFS(A1:A6,"Dog",B1:B6,"=0")

COUNTIFS can only be used in Excel 2007 or later.

Thanks for the reply. I forgot about a 3rd column that also needs to be looked at. The third column will have colors, i.e Black, White, Two Tone etc.

This would also for part of the count criteria. In some instances I want to have multiple colors added, i.e Beige, Brown.

Then you simply add another condition.

=SUMPRODUCT((A1:A6="Dog")*(B1:B6=0)*(C1:C6="White "))


For multiple choices you use a + symbol in SUMPRODUCT to represent OR:

=SUMPRODUCT((A1:A6="Dog")*(B1:B6=0)*(C1:C6="Beige ")+(C1:C6="Brown"))


If you need more help post an example workbook as it will make things much easier to tailor make to your needs.

Hi there,

Thank you, that solved my problem.

I've seen people use formulas with "--" eg.
=SUMPRODUCT(--(ARRAY1),--(ARRAY2)). And was wondering, why would you do this? And what function does the -- perform?

Have a look at this link. About half way down there is some information on the 'double unary' ( or -- )
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Wow thanks, that was really an interesting read...