Question in SUMs & SUMIF

If you use Filtering and subtotals you can get the same effect. Pivot tables will do what you ask. Other than that you'll need VBA.

Something like this (placed in your worksheet's module) should do the trick:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim rngIf As Range
    Dim varCriteria As Variant
    Dim rngSum As Range
    
    If Target.Count = 1 Then
        If Left(Target.Formula, 7) = "=SUMIF(" Then
            Set rngIf = Range(Mid(Target.Formula, 8, InStr(1, Target.Formula, ",") - 8))
            If InStr(InStr(1, Target.Formula, ",") + 1, Target.Formula, ",") = 0 Then
                varCriteria = Replace(Mid(Target.Formula, _
                    InStr(1, Target.Formula, ",") + 1, _
                    InStr(1, Target.Formula, ")") - InStr(1, Target.Formula, ",") - 1), """", "")
                Set rngSum = Range(Mid(Target.Formula, 8, InStr(1, Target.Formula, ",") - 8))
            Else
                Set varCriteria = Range(Mid(Target.Formula, _
                    InStr(1, Target.Formula, ",") + 1, _
                    InStr(InStr(1, Target.Formula, ",") + 1, Target.Formula, ",") - InStr(1, Target.Formula, ",") - 1))
                Set rngSum = Range(Mid(Target.Formula, _
                    InStr(InStr(1, Target.Formula, ",") + 1, Target.Formula, ",") + 1, _
                    InStr(1, Target.Formula, ")") - InStr(InStr(1, Target.Formula, ",") + 1, Target.Formula, ",") - 1))
            End If
            Call ShowValues(rngIf, varCriteria, rngSum, Target)
        End If
    End If
End Sub

Private Sub ShowValues(rngIf As Range, varCriteria As Variant, rngSum As Range, rngTarget As Range)

    Dim Result As String
    Dim Operator As String
    Dim Operators As Variant
    Dim i As Long
    
    Operator = "="
    Operators = Array(">=", "<=", "<>", "<", ">")
    
    For i = 0 To 4
        If InStr(1, varCriteria, Operators(i)) Then
            Operator = Operators(i)
            varCriteria = Replace(varCriteria, Operator, "")
        End If
    Next i
    
    varCriteria = Replace(varCriteria, "&", "")
    
    If Not IsError(Application.Evaluate(varCriteria)) Then
        varCriteria = Application.Evaluate(varCriteria)
    End If
    
    For i = 1 To rngIf.Count
        If Operator <> "=" Then
            If Application.Evaluate(rngIf(i, 1).Value & Operator & varCriteria) Then
                Result = Result & Application.Rept(" ", Len(Format(rngTarget.Value, "#,##0.00")) + 2 - Len(Format(rngSum(i, 1).Value, "#,##0.00"))) & Format(rngSum(i, 1).Value, "#,##0.00") & Chr(10) & Chr(13)
            End If
        Else
            If rngIf(i, 1).Value = varCriteria Then
                Result = Result & Application.Rept(" ", Len(Format(rngTarget.Value, "#,##0.00")) + 2 - Len(Format(rngSum(i, 1).Value, "#,##0.00"))) & Format(rngSum(i, 1).Value, "#,##0.00") & Chr(10) & Chr(13)
            End If
        End If
    Next i
    
    Result = Result & Application.Rept("-", Len(Format(rngTarget.Value, "#,##0.00")) + 2) & Chr(10) & Chr(13) & Application.Rept(" ", 2) & Format(rngTarget.Value, "#,###0.00")
    
    MsgBox Result

End Sub

Note that if your SUMIF function is pointing to an entire column, it may be slightly slow to calculate. Also note that this really only works for a simple SUMIF formula. If you have SUMIF nested inside another function, or another function nested inside SUMIF, then it will not work.

[Edit]
Oh, also note that the SUMRANGE part of SUMIF is no longer optional with the above. I'll make another version where it is optional and edit this post. Hang on.

[Edit the second]
Ok, updated the script for the SUMRANGE to be optional. Also, the CRITERIA parameter can now be a string. There are probably better ways to do this, but there it is.

See the attached for an example.