Multiple Condition Sum Across PivotTable & Another File Range

  • 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.



    Has anyone ever dealt with a similar challenge?


    Thank you.

  • 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.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Coding Sum If For Multiple Criteria


    Thank you.


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


  • Re: Coding Sum If For Multiple Criteria


    As to form,


    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.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • 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.

  • 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]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!