Count # items in ColA if blank in ColB

[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

 

[Max]

Something like this: =sumproduct((ColA="xxx")*(ColB=""))

 

[Nadine]

Unfortunately I got the result #NUM!. Thanks for trying.

 

[Joe User]

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.

 

[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=""))

 

[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.

 

[Eduardo]

Hi,
try

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

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.

 

[Nadine]

I'm getting #NAME?
Thanks for trying

Hi,
try

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

 

[Eduardo]

Are you using 2007

I'm getting #NAME?
Thanks for trying

 

[Eduardo]

Hi,
sorry my mistake I enter something wrong try

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

 

[Nadine]

Thank you! It worked.

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)





.

 

[Nadine]

Eduardo, Rich Rothstein gave me the formula to use. Thanks for all your help.