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
ColA and are blank in ColB. Any ideas?
Thanks.
Nadine
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
M
Max
Something like this: =sumproduct((ColA="xxx")*(ColB=""))
N
Nadine
Unfortunately I got the result #NUM!. Thanks for trying.
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=""))
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.
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,"=")
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
I'm getting #NAME?
Thanks for trying
Thanks for trying
Eduardo said:Hi,
try
=COUNTIFS(A2:A4,"Laptop",B2:B4,"=")
E
Eduardo
E
Eduardo
Hi,
sorry my mistake I enter something wrong try
=COUNTIFS(A2:A4,"Laptop",B2:B4,"")
sorry my mistake I enter something wrong try
=COUNTIFS(A2:A4,"Laptop",B2:B4,"")
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)
.
N
Nadine
Eduardo, Rich Rothstein gave me the formula to use. Thanks for all your help.