Counting blanks as zeros

  • Thread starter Thread starter bob
  • Start date Start date
B

bob

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
 
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$10000=0),
Chart1!$F$2:$F$10000)

If this post helps click Yes
 
Back
Top