CODEWITHSUNDEEP
excelexcel-formulaexcel-2010excel-2007
user41048
user41048

Reputation: 53

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 ?

Upvotes: 0

Views: 78

Answers (1)

EEM
EEM

Reputation: 6659

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)

Upvotes: 2

Related Questions