Sumif to return a blank if sum range is blank

[S0ck3t]

Seemingly simple but actually difficult?

I want a sumif formula that sums the target range if any of the cells
matched are not blank, if all of the cells matched are blank then it
should return a blank (and not a 0). If the sum range does have any
values (including 0!) then it should return the sum.

Example

Col to Match Col to Sum
A B
1
2 23
1
1
3 0
3

So in column C I need my sumif formula. It should return BLANK for all
rows with 1 in column A, 23 for row 2 and 0 for all rows with a 3 in
column A.


Any ideas?

 

[Ron Coderre]

Maybe this:

Note: formulas cannot make cells *blank*, but they can make cells *appear*
blank.

For a data range of A1:B10

If Col_A cells contain 2 and the corresponding Col_B values contain at least
one number, this formula returns the total. Otherwise it returns "".
C1: =IF(SUMPRODUCT((A1:A10=2)*ISNUMBER(B1:B10)),SUMIF(A1:A10,2,B1:B10),"")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Pete_UK]

You can keep your formula as it is (assuming it works correctly) and
apply a conditional format to the cell, such that if the value of the
cell is zero then the foreground colour should be made white - this
will then "appear" to be blank instead of showing zero.

Hope this helps.

Pete

 

[S0ck3t]

Thanks for your answers. In the end I decided that writing my own
replacement VBA function for SUMIF would be the simplest way (I have
some hefty formulas to put in the middle of this sumif function
anyway). For completeness I've posted the code below.

Public Function SumIfNB(rngSource As Range, vMatchVal As Variant,
rngSum As Range) As Variant
'This functions works like sumif but with one exception.
'If all matching values in the row to sum are blank it returns blank
On Error GoTo ErrHere:
Dim i As Long
Dim vSum As Variant
Dim bAllBlank As Boolean
bAllBlank = True

For i = 1 To rngSource.Cells.Rows.Count - 1
If rngSource.Cells(i, 1).Value <> "" Then
If rngSource.Cells(i, 1).Value = vMatchVal Then
If rngSum.Cells(i, 1).Value <> "" Then
bAllBlank = False
vSum = vSum + rngSum.Cells(i, 1).Value
End If
End If
End If
Next i

If bAllBlank = True Then
SumIfNB = ""
Else
SumIfNB = vSum
End If
ExitHere:
Exit Function

ErrHere:
MsgBox Err.Description
Resume ExitHere

End Function