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!!!!!!!!!!!!

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

wrote in message
oups.com...
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!!!!!!!!!!!!

=sumproduct(--(a1:a99="2"),--(b1:b99<""))

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

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

wrote:

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!!!!!!!!!!!!

--

Dave Peterson

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

" wrote:

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!!!!!!!!!!!!

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!

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

wrote in message
ps.com...
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!

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

" wrote:

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!

In the example It gave you three?

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

" wrote:

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!