Multiple Condition Sum Across PivotTable & Another File Range

samuels90 · Oct 9th 2007

Hey everyone,

I need to find a value in a pivot table with a range of values over 12 months (Book5) when 3 criteria are met, Branch#, LOB# and Month#. Sum(if() works for the 1st month, but I do not know how to do the coding when the month changes.

Attached are copies of the two files I am working with and my coding so far.

Code

Sub glvalueamount()
Dim glvalue As Integer
Dim glperiod As String


If (glperiod = "01") Then
glvalue = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C3=RC[-5],[Book5]Sheet1!R6C7:R34C7,0),0)))"
Else
If (glperiod = "02") Then
glvalue = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C3=RC[-5],[Book5]Sheet1!R6C7:R34C7,0),0)))"
If (glperiod = "03") Then
glvalue = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C3=RC[-5],[Book5]Sheet1!R6C7:R34C7,0),0)))"
If (glperiod = "04") Then
glvalue = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C3=RC[-5],[Book5]Sheet1!R6C7:R34C7,0),0)))"
If (glperiod = "05") Then
glvalue = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C3=RC[-5],[Book5]Sheet1!R6C7:R34C7,0),0)))"
If (glperiod = "06") Then
glvalue = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C3=RC[-5],[Book5]Sheet1!R6C7:R34C7,0),0)))"
If (glperiod = "07") Then
glvalue = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C3=RC[-5],[Book5]Sheet1!R6C7:R34C7,0),0)))"
If (glperiod = "08") Then
glvalue = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C3=RC[-5],[Book5]Sheet1!R6C7:R34C7,0),0)))"
If (glperiod = "09") Then
glvalue = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C3=RC[-5],[Book5]Sheet1!R6C7:R34C7,0),0)))"
If (glperiod = "10") Then
glvalue = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C3=RC[-5],[Book5]Sheet1!R6C7:R34C7,0),0)))"
If (glperiod = "11") Then
glvalue = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C3=RC[-5],[Book5]Sheet1!R6C7:R34C7,0),0)))"
If (glperiod = "12") Then
glvalue = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C3=RC[-5],[Book5]Sheet1!R6C7:R34C7,0),0)))"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If


End Sub

Display More

Has anyone ever dealt with a similar challenge?

Thank you.

shg · Oct 10th 2007

Re: Coding Sum If For Multiple Criteria

I can't tell what you're trying to do:

You're testing a string, glperiod, but it's never initialized.
You're doing something with an array formula, but not assigning it to a range, which is the only thing you can do it with.
You've defined an integer for some purpose ...
You're using an ever-deeper array of nested Ifs, which should all be a single Select Case statement.

Happy if I could help, but I don't see a place to start.

samuels90 · Oct 10th 2007

Re: Coding Sum If For Multiple Criteria

Thank you.

Perhaps you could help me to fix this Select Case coding.

Code

Select Case Range("M:M").Value
Range("M:M") = "01"
Range("R:R") = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C3=RC[-5],[Book5]Sheet1!R6C7:R34C7,0),0)))"
Range("M:M") = "02"
Range("R:R") = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C13=RC[-5],[Book5]Sheet1!R6C17:R34C17,0),0)))"
Range("M:M") = "03"
Range("R:R") = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C23=RC[-5],[Book5]Sheet1!R6C27:R34C27,0),0)))"
Range("M:M") = "04"
Range("R:R") = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C33=RC[-5],[Book5]Sheet1!R6C37:R34C37,0),0)))"
Range("M:M") = "05"
Range("R:R") = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C43=RC[-5],[Book5]Sheet1!R6C47:R34C47,0),0)))"
Range("M:M") = "06"
Range("R:R") = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C53=RC[-5],[Book5]Sheet1!R6C57:R34C57,0),0)))"
Range("M:M") = "07"
Range("R:R") = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C63=RC[-5],[Book5]Sheet1!R6C67:R34C67,0),0)))"
Range("M:M") = "08"
Range("R:R") = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C73=RC[-5],[Book5]Sheet1!R6C77:R34C77,0),0)))"
Range("M:M") = "09"
Range("R:R") = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C83=RC[-5],[Book5]Sheet1!R6C87:R34C87,0),0)))"
Range("M:M") = "10"
Range("R:R") = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C93=RC[-5],[Book5]Sheet1!R6C97:R34C97,0),0)))"
Range("M:M") = "11"
Range("R:R") = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C103=RC[-5],[Book5]Sheet1!R6C107:R34C107,0),0)))"
Range("M:M") = "12"
Range("R:R") = Selection.FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C113=RC[-5],[Book5]Sheet1!R6C117:R34C117,0),0)))"
End Select


End Sub

Display More

shg · Oct 10th 2007

Re: Coding Sum If For Multiple Criteria

As to form,

Code

Sub s90()


    With Range("R:R")
        Select Case Range("M:M").Value
            Case "01"
                .FormulaArray = "= SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C3=RC[-5],[Book5]Sheet1!R6C7:R34C7,0),0)))"
            Case "02"
                .FormulaArray = "=SUM(IF([Book5]Sheet1!R6C1:R34C1=RC[-4],IF([Book5]Sheet1!R6C2:R34C2=RC[-3],IF([Book5]Sheet1!R4C13=RC[-5],[Book5]Sheet1!R6C17:R34C17,0),0)))"
            '...
        End Select
    End With


End Sub

Display More

But I know you don't want to enter a 65,000 row array formula, so please explain what you're trying to do.

If no one helps you out with this this evening, I'll look back tomorrow.

samuels90 · Oct 10th 2007

Re: Coding Sum If For Multiple Criteria

I am trying to find the value to place in Column R of the "update records source" file based on the Branch#(column N), LOB#(column O), and the month (1-12) of the Pivot Table in the "Book 5" file.

Thank you for the help with the Select Case code.

Dave Hawley · Oct 10th 2007

Re: Coding Sum If For Multiple Criteria

samuels90, I would bet a PivotTable would do this for you in a jiff! I for one would never help you dig yourself into a spreadsheet hell hole, so I'm giving you some good solid advice when I say read EFFICIENT spreadsheet Designs

Why do you you always assume your answer rather than cleary & concisely stating what you are trying to do?

[fa]*[/fa]

Multiple Condition Sum Across PivotTable & Another File Range

Participate now!

Share