Formula For Counting Records

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

 

[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

 

[Teethless mama]

=SUMPRODUCT((A1:A100<>"")*(B1:B100=0)*(C1:C100=0))

 

[Teethless mama]

XL-2007:

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

 

[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

 

[tb]

Thanks for the formula help!
--
tb

=SUMPRODUCT((A1:A100<>"")*(B1:B100=0)*(C1:C100=0))