Thread: display sumif items in messagebox

I have a problem that I hope someone can help with. I have a sheet that gives a total using a sumif and I want to popup a message box that contains the values of the cells that were called by the sumif.

I have the code that will list all the cells in the range but not just the ones called by the sumif.

I have attached a screenshot of the sheet and the VBA code is below.

I have looked at precedents but in my real spreadsheet the sumif criteria key is on another sheet.

In the picture attached A8 contains the value SUM=(A1:A7) but should contain =SUMIF(B1:B7,D1,A1:A7) and the msgbox should contain a list of the values picked up.

Private Function RangeToString(ByRef rngDisplay As Range, ByVal strSeparator As String) As String

    'The string to separate elements on the message box,
    'if the range size is more than one cell
    
    Dim strMessage      As String
    Dim astrMessage()   As String
    
    Dim avarRange()     As Variant
    Dim varElement      As Variant
    
    Dim i               As Long
    
    'If the range is only one cell, we will return that that
    If rngDisplay.Cells.Count = 1 Then

        strMessage = rngDisplay.Value
    
    'Else the range is multiple cells, so we need to concatenate their values
    Else
    
        'Assign range to a variant array
        avarRange = rngDisplay
        
        'Loop through each element to build a one-dimensional array of the range
        For Each varElement In avarRange
        
            ReDim Preserve astrMessage(i)
            astrMessage(i) = CStr(varElement)
            i = 1 + i
            
        Next varElement
        
        'Build the string to return
        strMessage = Join(astrMessage, strSeparator)
        
    End If
    
    RangeToString = strMessage
    
End Function

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Target.Address(True, True, xlA1) = "$A$8" Then
    MsgBox RangeToString(Sheet2.Range("A1:A7"), vbCrLf), , "Items"
    End If
End Sub

If you're using code anyway, don't use SUMIF, loop through the values and build up a range where the condition is true. When you've processed the source range, you will be able to display the ouput range ... the cells that meet the criteria.

Regards

TMShucks has a very good point, but I didn't see it until I previewed my completed answer.

The following code will do what you want. It is specific to the sheet you have shown as an example. To generalize it, you will have to change the hard-coded references to cells A8 and D1 that you see in the first few lines.

You would put this code into the module for the sheet containing the data.

' This will show a message listing the values
' summed by the SUMIF in A8 every time the value
' in D1 is updated
Public Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = Range("D1").Address Then
 
      ' Get formula
      Dim f As String ' the formula
      f = Range("A8").Formula
 
      Dim msg As String ' the message you will display to the user
 
      Dim a As Variant ' an array to capture the arguments of SUMIF
      Const fPart As String = "=SUMIF("
 
      ' Break out the arguments of SUMIF
      a = Split(Mid(f, Len(fPart) + 1, Len(f) - Len(fPart) - 1), ",")
 
      Dim SUMIF_Range As String
      Dim SUMIF_Criteria As String
      Dim SUMIF_Sum_Range As String
 
      ' Assign arguments to variables for readability
      SUMIF_Range = a(0)
      SUMIF_Criteria = a(1)
      SUMIF_Sum_Range = a(2)
 
      Dim c As Range ' each cell in the SUMIF range
      Dim i As Long ' keep track of what row we are on within the range
      i = 0
      For Each c In Range(SUMIF_Range)
         i = i + 1
         If c.Value = Range(SUMIF_Criteria) Then
            msg = msg & CStr(Range(SUMIF_Sum_Range).Cells(i, 1)) & vbCrLf
         End If
      Next c
 
      MsgBox msg
 
   End If

OK. I persevered by trial and error and got what I needed in bringing up the bit from a date column.

If it helps anyone else trying to do something similar this is what I added. First I stuck a column of dates in D then at the end of the code where it used the following to select the data to be displayed in the messagebox:

msg = msg & CStr(Range(SUMIF_Sum_Range).Cells(i, 1)) & vbCrLf

msg = msg & CStr(Range(SUMIF_Sum_Range).Cells(i, 1)) & " " & CStr(Range(SUMIF_Sum_Range).Cells(i, 3)) & vbCrLf

It wasn't much of a mod but it achieved exactly what I needed. Just need help doing the part where clicking in the SUMIF cell (in my case A8) brings the messagebox up.

You can use these two UDF's.
ConcatIf mirrors SUMIF, with additional (optional) Delimiter and NoDuplicate arguments.
ThreeFormulas takes the result of the three arguments (formulas) and puts them in the cell, the input validation Title and the input validation Message.

In the attachment, E2 contains "North" and the formula in F2
=ThreeFormulas(SUMIF(A:A,E2,C:C), Concatif(A:A,E2,B:B,","), Concatif(A:A,E2,C:C,"+"))

puts the sum of all amounts (colC) that come from the North region (column A) in F2.
When F2 is selected, box will pop up with "Bob,Bob,Dave,Bob,Dave" as the title (the people in column B who contributed to that sum) and "10+15+8+56+12" as the message (the amounts that made up the sum).

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
                            Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    Dim i As Long, j As Long
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                                stringsRange.Column - compareRange.Column)
    
        For i = 1 To compareRange.Rows.Count
            For j = 1 To compareRange.Columns.Count
                If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                    If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                        ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                    End If
                End If
            Next j
        Next i
        ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function
    
Function ThreeFormulas(Formula1 As Variant, Optional Formula2 As Variant, Optional Formula3 As Variant) As Variant
    Dim formulaSnip As String, val2 As String, val3 As String
    Application.Volatile
    With Application.Caller
        formulaSnip = Mid(.Formula, 16)
        
        With .Validation
            .Delete
            .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop _
                    , Formula1:="=True", Operator:=xlBetween
             .ShowInput = True
             
            If Not IsMissing(Formula3) Then
                .InputTitle = CStr(Evaluate("CHOOSE(2," & formulaSnip))
                .InputMessage = CStr(Evaluate("CHOOSE(3," & formulaSnip))
            ElseIf Not IsMissing(Formula2) Then
                .InputMessage = CStr(Evaluate("CHOOSE(2," & formulaSnip))
            End If
        End With
    End With
    
    ThreeFormulas = Evaluate("CHOOSE(1," & formulaSnip)
End Function

mikerickson, that solution smells sweet. I will have to rewrite a fair bit of my sheet to test it but it looks like an elegant solution - I thank you for taking the time to provide it.
On initial playing I have a couple of questions. Where it displays bob,dave,bob,dave as a header and the amounts below as x+x+x+x is it possible to display as
bob - x
dave - x
bob - x
dave - x
The next question is that I will be looping through 4 sheets that represent each 1/4 of the year looking in the first instance for rent payments so I would like to display it as such
period ¦ rent
jan-mar 3,000
apr-jun 3,000
jul-sep 3,000
oct-dec 3,000

so that if for example you click in the first 3,000 it will show in the popup
11/1/09 - 1,000
12/2/09 - 1,000
10/3/09 - 1,000

Actually, you shouldn't re-write either of those UDFs!
They are generic UDFs that aren't specific to any workbook.
You may have to alter the arguments passed to them (the formula entered into cells), but re-writing the code is not indicated.

North and south were just the values that I used in the example DB.
The criteria argument of ConcatIf can be as varied as any criteria argument of SUMIF.

The thing that it can't do is return a concatenation of sub-strings from each row.
Like "Bob:10, Bob:15, Dave:8, Bob:56, Dave:12"
(If one introduced a helper column it could concatenate the cells of that column based on A:A meeting the criteria)

One could alter ConcatIf for a special use, but I'd like to alter it in a general fashion to deal with the equivalent of

=ConcatIf(A:A, E2, B:B&":"&C:C, ", ") to get that result.

ohhh, you show me how little I know! I will inspect the details and see if I can get it to do what I need. I understand what you mean, it just depends on if I have the ability to write the required arguments.
I will persevere down the new road you have sent me down. I thank you.

I have followed your advice and put a helper column in which CONCANETATEs column B and C then called it in the total cell's formula to display bob:10 etc in the pop up. All I need to do is action a vbCrLf line feed after each one and bob's your uncle. (or is it dave?)

Glad to have helped.

Thanks for the insight on how ConcatIf can be improved. (Altered to accept an array as the third argument, not just a range.)

Thanks.