I need to count the number of nonblank rows that have a certain
criteria in a different column. I assume that some version of the
SUMPRODUCT formula will do what I need to do, but I can't figure it
out for the life of me. Any ideas?

Say I have an array of data entered into cells K3:N35. I need to
count the number of rows that are nonblank in any of the cells in that
array that also have an "A" in column O. I think it should be
something like:

=SUMPRODUCT(--('Data Sheet'!K3:N35<""),--('Data Sheet'!O3:O35="A"))

Your assistance is most appreciated.

Carmen

What error do you get?

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

wrote in message
...
I need to count the number of nonblank rows that have a certain
criteria in a different column. I assume that some version of the
SUMPRODUCT formula will do what I need to do, but I can't figure it
out for the life of me. Any ideas?

Say I have an array of data entered into cells K3:N35. I need to
count the number of rows that are nonblank in any of the cells in that
array that also have an "A" in column O. I think it should be
something like:

=SUMPRODUCT(--('Data Sheet'!K3:N35<""),--('Data Sheet'!O3:O35="A"))

Your assistance is most appreciated.

Carmen

wrote:
I need to count the number of nonblank rows that have a certain
criteria in a different column. I assume that some version of the
SUMPRODUCT formula will do what I need to do, but I can't figure it
out for the life of me. Any ideas?

Say I have an array of data entered into cells K3:N35. I need to
count the number of rows that are nonblank in any of the cells in that
array that also have an "A" in column O. I think it should be
something like:

=SUMPRODUCT(--('Data Sheet'!K3:N35<""),--('Data Sheet'!O3:O35="A"))

Your assistance is most appreciated.

Carmen

SUMPRODUCT requires the arrays have the same dimensions.

This might be easier if you pull one of the constraints out to a helper
column. E.g., in column Q:

=SUMPRODUCT(--(K3:N3=""))<4

Then the result is

=SUMPRODUCT(--(Q3:Q35),--(O3:O35="A"))

I'm assuming you want to count a row if there is at least one entry in that
row.

=SUMPRODUCT(--(MMULT(--(K3:N35<""),{1;1;1;1})0),--(O3:O35="A"))

--
Biff
Microsoft Excel MVP


wrote in message
...
I need to count the number of nonblank rows that have a certain
criteria in a different column. I assume that some version of the
SUMPRODUCT formula will do what I need to do, but I can't figure it
out for the life of me. Any ideas?

Say I have an array of data entered into cells K3:N35. I need to
count the number of rows that are nonblank in any of the cells in that
array that also have an "A" in column O. I think it should be
something like:

=SUMPRODUCT(--('Data Sheet'!K3:N35<""),--('Data Sheet'!O3:O35="A"))

Your assistance is most appreciated.

Carmen

On May 5, 9:28*pm, "T. Valko" wrote:
I'm assuming you want to count a row if there is at least one entry in that
row.

=SUMPRODUCT(--(MMULT(--(K3:N35<""),{1;1;1;1})0),--(O3:O35="A"))

--
Biff
Microsoft Excel MVP

wrote in message

...



I need to count the number of nonblank rows that have a certain
criteria in a different column. *I assume that some version of the
SUMPRODUCT formula will do what I need to do, but I can't figure it
out for the life of me. Any ideas?

Say I have an array of data entered into cells K3:N35. *I need to
count the number of rows that are nonblank in any of the cells in that
array that also have an "A" in column O. *I think it should be
something like:

=SUMPRODUCT(--('Data Sheet'!K3:N35<""),--('Data Sheet'!O3:O35="A"))

Your assistance is most appreciated.

Carmen- Hide quoted text -

- Show quoted text -

Biff,

This does it! Thanks so much. You totally saved my project. 8-D

Carmen

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


wrote in message
...
On May 5, 9:28 pm, "T. Valko" wrote:
I'm assuming you want to count a row if there is at least one entry in
that
row.

=SUMPRODUCT(--(MMULT(--(K3:N35<""),{1;1;1;1})0),--(O3:O35="A"))

--
Biff
Microsoft Excel MVP

wrote in message

...



I need to count the number of nonblank rows that have a certain
criteria in a different column. I assume that some version of the
SUMPRODUCT formula will do what I need to do, but I can't figure it
out for the life of me. Any ideas?

Say I have an array of data entered into cells K3:N35. I need to
count the number of rows that are nonblank in any of the cells in that
array that also have an "A" in column O. I think it should be
something like:

=SUMPRODUCT(--('Data Sheet'!K3:N35<""),--('Data Sheet'!O3:O35="A"))

Your assistance is most appreciated.

Carmen- Hide quoted text -

- Show quoted text -

Biff,

This does it! Thanks so much. You totally saved my project. 8-D

Carmen

T. Valko wrote:
I'm assuming you want to count a row if there is at least one entry in that
row.

=SUMPRODUCT(--(MMULT(--(K3:N35<""),{1;1;1;1})0),--(O3:O35="A"))


Nice solution Biff! And thanks for writing about MMULT... this was
unknown to me, and I suspect many others as well.

Thanks!

--
Biff
Microsoft Excel MVP


"smartin" wrote in message
...
T. Valko wrote:
I'm assuming you want to count a row if there is at least one entry in
that row.

=SUMPRODUCT(--(MMULT(--(K3:N35<""),{1;1;1;1})0),--(O3:O35="A"))


Nice solution Biff! And thanks for writing about MMULT... this was unknown
to me, and I suspect many others as well.