Sumif to return a blank if sum range is blank

S

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?
 
G

Guest

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
 
P

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
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top