Formula query

Neal · July 6th, 2006, 04:57 AM

Hi,

How do I return an offset value within a named range using a formula?

For example, my range "RngTest" is from C3:J43.
If a value of "Product123" is found within the range, (lets say its found
in cells C3; E5 and E7), I need the accumulative values in the cells 'below'
(in cells D3; F5 and F7) added (SUM) and returned to cell L3.

Therefore if Cells D3; F5; and F7 have the respective values of 3; 4 and 5,
the value of cell L3 should be 12.

Also, do I need to express "RngTest" or "C3:J43" or does it matter?

Thanks in advance,

Neal

A Northern Soul

maccas · July 6th, 2006, 05:10 AM

I think this is the sort of thing you're after

Code:

Sub Test()

Dim rngTest As Range
Dim Cell As Range
Dim sngTotal As Single
Dim strToFind As String

    ' Set test range
    Set rngTest = ThisWorkbook.Sheets("Sheet1").Range("C3:J43")

    ' Set find String
    strToFind = "Product123"

    ' Loop through every cell in the test range
    For Each Cell In rngTest

        ' If our current cell has the find string as its value then...
        If Cell.Value = strToFind Then

            ' Only add to the total if the offset cell is a number
            If IsNumeric(Cell.Offset(1, 0).Value) Then sngTotal = sngTotal + Cell.Offset(1, 0).Value

        End If

    Next Cell

    ' Output result
    rngTest.Parent.Range("L3").Value = sngTotal

End Sub

Maccas

Neal · July 6th, 2006, 05:52 AM

Hi Maccas,

Thanks for the Sub-routine.
However, what I needed was a formula for cell "L3".

I found some helpful advice ....

=SUMIF(C3:J43,"product123",D3:K43)

Neal

A Northern Soul

maccas · July 6th, 2006, 06:44 AM

Try this UDF then:

Code:

Public Function OffsetSum(rngSearchRange As Range, strToFind As String, Optional RowOffset As Integer = 1, Optional ColumnOffset As Integer = 0)

Dim Cell As Range
Dim sngTotal As Single

    ' Loop through every cell in the search range
    For Each Cell In rngSearchRange

        ' Only look at cells where there is no potential for self-reference
        If Intersect(Application.Caller, Cell) Is Nothing _
            And Intersect(Application.Caller, Cell.Offset(RowOffset, ColumnOffset)) Is Nothing Then

            ' If our current cell has the find string as its value then...
            If Cell.Value = strToFind Then

                ' Only add to the total if the offset cell is a number
                If IsNumeric(Cell.Offset(RowOffset, ColumnOffset).Value) Then sngTotal = sngTotal + Cell.Offset(RowOffset, ColumnOffset).Value

            End If

        End If

    Next Cell

    ' Output result
    OffsetSum = sngTotal

End Function

You can call it as a function in a cell of a workbook.

Maccas

jrogers · July 6th, 2006, 07:27 AM

couldn't this be done with a sumif.. ie in cell L2 type the value you are looking for ie 'Product12'

then in cell L3 type

Code:

=sumif(RngTest,L2,SumRngTest)

you need to either define SumRngTest as C4:J43, or just put C4:J43 in the formula

I think you found your solution while I was typing mine..

maccas · July 6th, 2006, 08:04 AM

I didn't realise you could do SUMIFs in two dimensions. That's a much more elegant solution and vastly preferable to my own.

Maccas