Formula For Counting Records

T

tb

Column A of my worksheet has a bunch of part numbers. Columns B and C have
formulas that show quantities based on data coming from other worksheets via
a link.
What I would like to do, is put a formula in cell D1 that counts all the
records based on the following criteria:

If the cell in Col. A is not blank, and if the quantities in both Cell B and
C are equal to zero, then count that record.

So, I created the following formula (which obviously does not work...):
=countif(a:a,and(a<>"",and(b="0",c="0")))

I am looking for a dynamic formula that will work no matter how many records
are in Cols A,B,C.

Thanks.
 
B

barry houdini

Column A of my worksheet has a bunch of part numbers.  Columns B and C have
formulas that show quantities based on data coming from other worksheets via
a link.
What I would like to do, is put a formula in cell D1 that counts all the
records based on the following criteria:

If the cell in Col. A is not blank, and if the quantities in both Cell B and
C are equal to zero, then count that record.

So, I created the following formula (which obviously does not work...):
=countif(a:a,and(a<>"",and(b="0",c="0")))

I am looking for a dynamic formula that will work no matter how many records
are in Cols A,B,C.

Thanks.

What version of Excel do you have? For Excel 2007 only you can use a
COUNTIFS formula which can reference whole columns, i.e.

=COUNTIFS(A:A,"<>",B:B,0,C:C,0)

For earlier versions of Excel you can use SUMPRODUCT something like
this (you can't reference whole columns)

=SUMPRODUCT((A1:A1000<>"")*(B1:B1000=0)*(C1:C1000=0)*(B1:B1000<>"")*
(C1:C1000<>""))

Note the additional checks to verify that columns B and C are not
blank....because this version will count true blanks as zeroes.....

Increase the range as far as you want, blank cells won't
interfere......or if you really want to restrict the formula to the
used ranges then have a look at dynamic names

regards, barry
 
T

tb

That works, thanks!
--
tb

Column A of my worksheet has a bunch of part numbers. Columns B and C have
formulas that show quantities based on data coming from other worksheets
via
a link.
What I would like to do, is put a formula in cell D1 that counts all the
records based on the following criteria:

If the cell in Col. A is not blank, and if the quantities in both Cell B
and
C are equal to zero, then count that record.

So, I created the following formula (which obviously does not work...):
=countif(a:a,and(a<>"",and(b="0",c="0")))

I am looking for a dynamic formula that will work no matter how many
records
are in Cols A,B,C.

Thanks.

What version of Excel do you have? For Excel 2007 only you can use a
COUNTIFS formula which can reference whole columns, i.e.

=COUNTIFS(A:A,"<>",B:B,0,C:C,0)

For earlier versions of Excel you can use SUMPRODUCT something like
this (you can't reference whole columns)

=SUMPRODUCT((A1:A1000<>"")*(B1:B1000=0)*(C1:C1000=0)*(B1:B1000<>"")*
(C1:C1000<>""))

Note the additional checks to verify that columns B and C are not
blank....because this version will count true blanks as zeroes.....

Increase the range as far as you want, blank cells won't
interfere......or if you really want to restrict the formula to the
used ranges then have a look at dynamic names

regards, barry
 

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