SumIf across multiple sheets if a value is found

[vzq032372]

Hi All, I'm hoping someone can help me....

I have a few sheets that all look the same but there's a value in K2 on some of the sheets that I need to reference to sum up the values.

For example:
on my Summary sheet, C2 =
1) loop through all the sheets and find "Red" in K2
2) If Red is found in K2, sum the value in Summary!C2 for those sheets

I found the following and tried to do cut it up to use the values in a formula rather than a msgbox

Dim ws As Worksheet
Dim strWhat As String
Dim rngSearch As Range
Dim rngFound As String
Dim i As Integer

strWhat = "Red"
For Each ws In Worksheets
    Set rngSearch = ws.Cells.Find(What:=strWhat)
    If Not rngSearch Is Nothing Then
        i = i + 1
        If i = 1 Then
            rngFound = rngSearch.Worksheet.Name
        Else
            rngFound = rngFound & ", " & rngsearch.worksheet.name/']rngSearch.Worksheet.Name
        End If
    End If
Next ws


MsgBox "'" & strWhat & "' found on the following worksheet(s): " & rngFound & "."

When I added a formula with "rngFound" it returned with a ?NAME error

Any suggestions?

 

[aRandomHelper]

but there's a value in K2 on some of the sheets that I need to reference to sum up the values.

2) If Red is found in K2, sum the value in Summary!C2 for those sheets

You mentioned there's a value in K2 that you need to sum, yet you are looking for "Red" in K2? This part is confusing. You can't sum strings.

In your code, there's a slash at the end, might be causing the error: rngsearch.worksheet.name/

 

[vzq032372]

If the Text in K2 = "RED", sum up all the values in C5 across all the sheets where K2 is red

 

[aRandomHelper]

Apologies for the late response, have been busy with other stuff the past few days.

If I understood you correctly, please try the following:

    Dim ws As Worksheet
    Dim strWhat As String
    Dim sumValue As Double
    
    strWhat = "Red"
    For Each ws In Worksheets
        If ws.Cells(2, "K").Value = strWhat Then
            sumValue = sumValue + ws.Cells(5, "C").Value
        End If
    Next ws
    
    Sheets("Summary").Cells(2, "C").Value = sumValue

If looks in K2 of every sheet, if strWhat ("Red") is found, then sums the value in C5 of that sheet to sumValue. At the end of the loop, the final sumValue is then placed in C2 of Summary sheet.