CODEWITHSUNDEEP
if-statementgoogle-sheetsarray-formulascountifsumifs
tryingout
tryingout

Reputation: 39

SUMIFS ARRAYFORMULA returns blank cell if the value is 0, how do I force it to show 0

Here's my formula for the cell K37:

=IFERROR(1/(1/ArrayFormula(sumifs($H37:$H,$E37:$E,"ABC",MONTH($G37:$G),1))))

the cell K37 returns nothing (it's blank) when there's no cells in $E37:$E range with the "ABC" criteria and some value in corresponding $H37:$H range's cell. And it's fine!
But how do I force the formula to return 0 instead of just a blank cell if there's at least one cell in $E37:$E range, satisfying the "ABC" criteria with the actual $0.00 value in corresponding $H37:$H range's cell?

                 E           G            H           K 
37          ABC       Jan-1     $0.00      blank (but I want it to be 0)
38
39

Upvotes: 0

Views: 425

Answers (1)

Terio
Terio

Reputation: 507

A way:

K37

=ARRAYFORMULA(IF(COUNTIFS($E37:$E,"ABC",MONTH($G37:$G),1),IFERROR(1/(1/SUMIFS($H37:$H,$E37:$E,"ABC",MONTH($G37:$G),1)),0),""))

Check if value exists, if #DIV/0! return 0, otherwise return blank.

Upvotes: 3

Related Questions