Sum cells offset from another

[DrH100]

Hi,
I'm looking for a formula that can look through a worksheet, find a value and add together the cells next to it.

In essence I have a sheet that could have a value eg Oranges a number of times anywhere on it.

What I would like to be able to do is have a formula that looks for all the occurrences of "Oranges" and adds together the cells next to them. Ie if "Oranges" is in A1, D5, G32 I would want the total of the number in B1, E5 and H32.

Theoretically there is no limit to the number of times "Oranges" could appear but also no structure to the data (other than the next cell will always be a number)

I suppose I could use VBA to look through but was hoping there might be someway of doing it with a formula (Sumif and OFFSET maybe) as there may well be a need to look for different values at different times

Any thoughts appreciated.

 

[jasonb75]

Based on your examples

=SUMIF(B:H,"Oranges",A:G)

nothing else needed. As long as both the sum range and criteria range are of equal size, sumif will work fine.

 

[Georgiboy]

Anywhere on the sheet can be tricky as the sheet is rather large, i have created a find loop to test. It would not matter where the values were in the sheet.
I know you stated formula but thought i would leave it here anyway:

Sub FindInRange()
    Dim howManyInRange As Long
    Dim foundCount As Long
    Dim oFindRange As Range
    Dim rngSearch As Range
    Dim srchVal As String
    Dim rSum As Double
   
    srchVal = "Oranges"
    Set rngSearch = Sheets("Sheet1").Cells
   
    howManyInRange = Application.WorksheetFunction.CountIf(rngSearch, srchVal)
   
    Set oFindRange = Range("A1")
    If Not howManyInRange = 0 Then
        Do
            Set oFindRange = rngSearch.Find(what:=srchVal, After:=oFindRange)
            foundCount = foundCount + 1
            rSum = rSum + oFindRange.Offset(, 1).Value
        Loop While Not foundCount >= howManyInRange
    End If
   
    MsgBox "Oranges sum: " & rSum
End Sub

The good thing about this method is it will not be bogged down if the value is found in row 999,999 as it uses find.

 

[DrH100]

Based on your examples

=SUMIF(B:H,"Oranges",A:G)

nothing else needed. As long as both the sum range and criteria range are of equal size, sumif will work fine.

Thanks very much - over thinking it as always.
I had to flip the ranges around but then it worked so thanks very much

 

[DrH100]

Anywhere on the sheet can be tricky as the sheet is rather large, i have created a find loop to test. It would not matter where the values were in the sheet.
I know you stated formula but thought i would leave it here anyway:

Sub FindInRange()
    Dim howManyInRange As Long
    Dim foundCount As Long
    Dim oFindRange As Range
    Dim rngSearch As Range
    Dim srchVal As String
    Dim rSum As Double
  
    srchVal = "Oranges"
    Set rngSearch = Sheets("Sheet1").Cells
  
    howManyInRange = Application.WorksheetFunction.CountIf(rngSearch, srchVal)
  
    Set oFindRange = Range("A1")
    If Not howManyInRange = 0 Then
        Do
            Set oFindRange = rngSearch.Find(what:=srchVal, After:=oFindRange)
            foundCount = foundCount + 1
            rSum = rSum + oFindRange.Offset(, 1).Value
        Loop While Not foundCount >= howManyInRange
    End If
  
    MsgBox "Oranges sum: " & rSum
End Sub

The good thing about this method is it will not be bogged down if the value is found in row 999,999 as it uses find.

Thanks very much. "Anywhere on the sheet" was a bit vague and I'm sure when I get the final dataset it will be much smaller than that but was thinking of all eventualities but thanks for this as if it is too big I may well give this a go. Thanks very much

 

[jasonb75]

I had to flip the ranges around but then it worked

Oops, I've got mixed up with SUMIF and SUMIFS again, glad you were able to figure it out

A quick heads up on the vba alternative, with a large data range and only a few cells meeting the criteria it should work great, if you have a lot of cells meeting the criteria then it may be slow. I made the mistake of looping on a range.find with a lot of matches once and remember it taking ages, unfortunately I don't recall what this was for so I'm not able to look for it and see if I found a faster way (don't think I did though).

 

[DrH100]

Oops, I've got mixed up with SUMIF and SUMIFS again, glad you were able to figure it out

A quick heads up on the vba alternative, with a large data range and only a few cells meeting the criteria it should work great, if you have a lot of cells meeting the criteria then it may be slow. I made the mistake of looping on a range.find with a lot of matches once and remember it taking ages, unfortunately I don't recall what this was for so I'm not able to look for it and see if I found a faster way (don't think I did though).

Thanks very much