Countif, IF condition is met

[SalientAnimal]

Hi All...

I know somewhere I have done this before, but for some reason I am no
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
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
should get 3.

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

 

[Spencer101]

Hi All...

I know somewhere I have done this before, but for some reason I am no
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
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
should get 3.

I used =IF(A1:A6="Dog",(COUNTIF(B1:B6,">=0"))). This however gives m
the incorrect results. When I do the calculation as show I get 4 instea
of 3. It is counting all the values that are greater than or equal to
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

 

[SalientAnimal]

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

Wow thanks, that was really an interesting read..