Formula For Counting Records

  • Thread starter Thread starter tb
  • Start date Start date
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.
 
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.
Click to expand...

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
 
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.
Click to expand...

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
 
Back
Top