Countif multiple criteria within the same column

[Guest]

I need to count different model numbers within the same column. The model
numbers might be BLR, GHTR, HYRS,GUIT and so on. I need excel to count the
different model numbers in this column but only if another column = "N" Any
suggestions?

 

[Dave Peterson]

=sumproduct(--(a1:a10="BLR"),--(b1:b10="N"))

change the range, but don't use the whole column.

 

[Guest]

I dont' believe I explained this correctly. For example:

Column A Column B
BHTY N
BHRY N
LNTY N
GHRE U
GHRT N
LNUY U
LNJH N
BHHR N
BHHT U
LNTU N
TYRU N
BH GH LN TY
3 1 3 1

I need to break these down into 4 categories. There might be as many as 8
models for one category.
Thanks.

 

[Guest]

You could set up a third column (Col C) and enter

=IF(B1="N",A1,"")

copy down. then below this new column enter

=SUMPRODUCT(1/COUNTIF(C1:C6,C1:C6))

adjust ranges as necessary. I tried to do this in one array formula, but
couldn't quite get it.

 

[Guest]

sorry - left off the last half of that formula

=SUMPRODUCT(1/COUNTIF(C1:C6,C1:C6))-(--(COUNTIF(C1:C6,"")>0))

 

[Domenic]

Assuming that A1:B11 contains your data, let D1:G1 contain BH, GH, LN,
and TY, and let C2 contain N, then enter the following formula in D2 and
copy across...

=SUMPRODUCT(--(LEFT($A$1:$A$11,2)=D$1),--($B$1:$B$11=$C2))

Hope this helps!