SUMIF to return blank if value not available

[maw230]

It appears that my SUMIF formulas always returns 0 even if there are no values available. The problem is that there are cases when the SUMIF could legitimately return a 0 and I need to distinguish between the two instances, so that other formulas will work. If the SUMIF is truly 0 then I need to see the 0, but if there are no values available for the SUMIF then I would like to see a blank cell.

An example SUMIF:

Please Login or Register  to view this content.

In the case above, there are no values in 'AR_test' for the conditions specified, so I would like to see a blank cell. However, if there are values and the values sum to 0 then I need to see the 0. I hope that makes sense. Thanks.

[AliGW]

Try this:

=iferror(SUMIF(AR_test!$A:$R,CONCATENATE($B$3,R$3),AR_test!$G:$G),"")

[ben_hensel]

Please Login or Register  to view this content.

Like that?

[JieJenn]

Maybe =IF(COUNTIF(AR_test!$A:$R,CONCATENATE($B$3,R$3))=0,"",SUMIF(AR_test!$A:$R,CONCATENATE($B$3,R$3),AR_test!$G:$G))

[maw230]

Thanks everyone. IFERROR won't work because there is no error. COUNTIF is perfect as long as I understand it correctly, and I think both of your formulas will work. COUNTIF searches the range for my concatenation and if it isn't there it returns a blank? Sounds good to me.

[ben_hensel]

JieJenn and I actually had slightly different logical tests.

JJ's returns a blank when there are exactly zero cells with the concatenation in range and the sum otherwise,
Mine returns the sum when there are more than zero cells with the concatenation in the range, and blank otherwise.

So technically the logical test will give different results when the COUNTIF is below zero; but since unmodified COUNTIF can't do that, it doesn't really matter.

[maw230]

Sounds good. Thanks again.