Search code examples
excelexcel-formulaexcel-2010excel-2007

Excel not reading the empty fomulate cell as blank

I wrote a formula

=COUNTIFS(Excel_Export_IT_infrastructure!CF:CF,"<="&O4,Excel_Export_IT_infrastructure!CF:CF,">="&TODAY(), Excel_Export_IT_infrastructure!CM:CM,"<>Orphan",Excel_Export_IT_infrastructure!CG:CG,"<>",Excel_Export_IT_infrastructure!BC:BC,"=B")

Now the problem is Excel_Export_IT_infrastructure!CG:CG,"<>" is not actually ignoring the blanks as the cell CG has hidden formula . I guess its not considering blank.

How to make Excel_Export_IT_infrastructure!CG:CG,"<>" to filter blank. CG column might have foumalte cell but empty ?

Solution

  • Try this formula:

    =SUMPRODUCT(
(Excel_Export_IT_infrastructure!$CF:$CF<=$O4)*1,
(Excel_Export_IT_infrastructure!$CF:$CF>=TODAY())*1,
(Excel_Export_IT_infrastructure!$CM:$CM<>"Orphan")*1,
(Excel_Export_IT_infrastructure!$CG:$CG<>"")*1,
(Excel_Export_IT_infrastructure!$BC:$BC="B")*1)