Count IF

[Romanli]

I want to count text/numbers in a range of cells based on a certain criteria
only the informaiton in another column (same row) meets specific criteria.

 

[Bernard Liengme]

You want to COUNT or SUM?
See COUNTIF and SUMIF in Help, then coma back with more questions
Also have a look at SUMPRODUCT
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
best wishes

 

[Romanli]

=IF(Installed!J151:J158,US,COUNTIF(Installed!N151:N158,green))

If item in one column =US, then count if item in second column =Green.

This is what I want (in theory anyway), but the way I have typed it gives me
a value error.

I definly only want counts, becuase it is all text.

 

[Dave Peterson]

=sumproduct(--(installed!j151:j158="US"),--(installed!n151:n158="Green"))

Adjust the ranges to match--but you can't use whole columns.

=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


If you're using xl2007, you may want to look at =countifs().

 

[Bernard Liengme]

"but you can't use whole columns" except in XL2007
Bernard

 

[Romanli]

THANKS! It worked!

"but you can't use whole columns" except in XL2007
Bernard

 

[Dave Peterson]

Thanks for the addendum, Bernard.

(I copied from a very old post <bg>.)