Count # items in ColA if blank in ColB

N

Nadine

I need to count the number of items in ColA that match a certain criteria in
ColA and are blank in ColB. Any ideas?
Thanks.
Nadine
 
J

Joe User

Nadine said:
I need to count the number of items in ColA
that match a certain criteria in ColA and are
blank in ColB.

You do not give sufficient information to provide a solution that you can
enter verbatim. Try something like:

=sumproduct((A1:A100="criteria")*(B1:B100=""))

That assumes a simple criteria based on matching text. If the criteria is
numeric, you would exclude the double-quotes. If the criteria is not a
simple comparison (equal, less than, greater than, etc), you may need to be
more specific if you cannot apply the above paradigm.
 
R

Rick Rothstein

I'm assuming you are using XL2003 or earlier. If so, you cannot use whole
column (such as A:A) references within the SUMPRODUCT function... you have
to specify a fixed range and all ranges should be the same length (there are
some exceptions to this, but not for the simple ranges you are using). So,
try something like this...

=SUMPRODUCT((A2:A1000="xxx")*(B2:B1000=""))
 
N

Nadine

ColA is a hardware type and ColB is other data where I want to count the
blanks if they match the criteria in ColA. This is to summarize the rows and
rows of data.

So: How do I count all the laptops in ColA where ColB is blank?

Thanks.
 
E

Eduardo

Hi,
try

=COUNTIFS(A2:A4,"Laptop",B2:B4,"=")

Nadine said:
ColA is a hardware type and ColB is other data where I want to count the
blanks if they match the criteria in ColA. This is to summarize the rows and
rows of data.

So: How do I count all the laptops in ColA where ColB is blank?

Thanks.
 
N

Nadine

Thank you! It worked.

Rick Rothstein said:
I'm assuming you are using XL2003 or earlier. If so, you cannot use whole
column (such as A:A) references within the SUMPRODUCT function... you have
to specify a fixed range and all ranges should be the same length (there are
some exceptions to this, but not for the simple ranges you are using). So,
try something like this...

=SUMPRODUCT((A2:A1000="xxx")*(B2:B1000=""))

--
Rick (MVP - Excel)





.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top