Counting blanks as zeros
 

Column AZ contains zeroes as well as blank cells (meaning no value has been
entered in the cell). In my formula below, I want to reference only the cells
that contain zero and ignore the cells that are blank. As written, the
formula is referencing both zero and blak cells. How can I modify the formula
to do ignore the blank cells in column AZ?

{=SUM(IF(Chart1!$A$2:$A$10000=A3,IF(Chart1!$C$2:$C $10000=B3,IF(Chart1!$AZ$2:$AZ$10000=0,Chart1!$F$2: $F$10000),)))}

Thanks,
Bob

Counting blanks as zeros
 

You can add one more condition Chart1!$AZ$2:$AZ$10000<"" or use SUMPRODUCT()
formula as below...non-array entered

=SUMPRODUCT((Chart1!$A$2:$A$10000=A3)*(Chart1!$C$2 :$C$10000=B3)*
(Chart1!$AZ$2:$AZ$10000<"")*(Chart1!$AZ$2:$AZ$100 00=0),
Chart1!$F$2:$F$10000)

If this post helps click Yes
---------------
Jacob Skaria


"bob" wrote:

Column AZ contains zeroes as well as blank cells (meaning no value has been
entered in the cell). In my formula below, I want to reference only the cells
that contain zero and ignore the cells that are blank. As written, the
formula is referencing both zero and blak cells. How can I modify the formula
to do ignore the blank cells in column AZ?

{=SUM(IF(Chart1!$A$2:$A$10000=A3,IF(Chart1!$C$2:$C $10000=B3,IF(Chart1!$AZ$2:$AZ$10000=0,Chart1!$F$2: $F$10000),)))}

Thanks,
Bob