+ Reply to Thread
Results 1 to 4 of 4

Sumif to return a blank if sum range is blank

  1. #1
    S0ck3t@yahoo.com
    Guest

    Sumif to return a blank if sum range is blank

    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?


  2. #2
    Ron Coderre
    Guest

    RE: Sumif to return a blank if sum range is blank

    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


    "S0ck3t@yahoo.com" wrote:

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


  3. #3
    Pete_UK
    Guest

    Re: Sumif to return a blank if sum range is blank

    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


  4. #4
    S0ck3t@yahoo.com
    Guest

    Re: Sumif to return a blank if sum range is blank

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1