Please help with Count or Countif...

K

kevin_besner

Hi all. I did try searching this group for answers, but couldn't find
an example for exactly what I'm trying to do, although I'm sure it is
quite simple (I'm and excel rookie...).

Two columns, A & B:
1 n
1 a
1
2
2 c
2
1
1 y

How do I do a count of how many rows have the "2" (text, not number)
in column A -and- have non-blank in column B (so any text value in
column B)?
So in the above case, I would want the result to be a count of 1, for
the row that had 2 c.......

Thanks!!!!!!!!!!!!
 
D

David Biddulph

=SUMPRODUCT((A1:A8="2")*(B1:B8<>""))
or
=SUMPRODUCT(--(A1:A8="2"),--(B1:B8<>""))
 
B

bj

Try
=sumproduct(--(A1:A100="2"),--(B1:B100<>""))
the --( changes the logical true false to numeric 1 0.
the arrays have to be the same size but cannot be a full column, at least in
pre 2007
 
K

kevin_besner

Thank you, the
=sumproduct(--(A1:A100="2"),--(B1:B100<>""))

worked!

Next question though.......what if I want to do the same sort of
thing, except this time I only want to include where column B -is-
blank?
I tried substituting and equal sign instead of <>, but it just picks
up every row....
thx again!
 
D

David Biddulph

= instead of <> should do the trick. You need to check your formula again
if it's counting every row.
 
B

bj

I don't know why the equal would not work
but try
=sumproduct(--(A1:A100="2"),--not(B1:B100<>""))
 
B

bj

In the example It gave you three?

another method would be
=countif(A1:A100,"2")-sumproduct(--(A1:A100="2"),--(B1:B100<>""))
 
Top