CountIF

[Kell2604]

Hello -
I think I need the COUNTIF function for this but am not getting it to work.
Help!
I need to count a range of cells, if the cell next to it contains a certain
value.

Comm Spares
Military OEM
Bus Jet OEM 1
Bus Jet OEM 1
Bus Jet OEM 1
Bus Jet OEM
Comm Spares
Comm Spares 1

So, I want the cell containing my formula to count the 1's if the cell to
it's left = Bus Jet OEM. I will have another cell counting the 1's if the
cell to it's left = Comm Spares and so on.
Hope my explanation makes sense.

Thanks in advance for your help!!
Kelley

 

[Mike]

=SUMPRODUCT((A1:A5="Bus Jet OEM")*(B1:B5=1))

 

[Kell2604]

Thanks for your help Mike but I missed a part of my problem. The numerical
column doesn't just have the number 1 in it (I was trying to make my
explanation simple) it has 1.1 & 1.2 & 1.7 etc. I need to count them all as
if they were 1. Basically I just want to count the number of cells that have
any value or number in them. So I'm having trouble using the SUMPRODUCT
because they are not all equal to 1.
Thanks again for your help - sorry for the confusion!

 

[John C]

Use the INT function in your formula that Mike gave you, like so:
=SUMPRODUCT((A1:A5="Bus Jet OEM")*(INT(B1:B5)=1))

 

[Dave Peterson]

Maybe...

=SUMPRODUCT(--(A1:A5="Bus Jet OEM"),--(isnumber(B1:B5)))

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Kell2604]

Dave - Does isnumber just mean that it will count any cell that has a number
in it in the range b1 to b5??

Just want to make sure I understand what it's doing.

Thanks!! Kelley

 

[Kell2604]

Hi Again,

I think I got it working by using B1:B5>0.

Thanks Again - you guys are the best!

 

[Dave Peterson]

Yep.

(I guessed that's what you wanted. I may have guessed wrong.)

 

[Kell2604]

Yes, that's exactly what I wanted!!

Thank you VERY much!!

 

[Mike]

=SUMPRODUCT((A1:A5="Bus Jet OEM")*(INT(B1:B5)=1))
This will count 1.1 thru 1.9