Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Conditional Summing with 2 variables (XP & 2003)

    I have used conditional summing with 2 variable in the past but on the attached worksheet I cannot make it work or understand why it isn't working. The variables to find are in col C 13 thru 79 for Location and col G 13 thru 79 for Agency . Col L 13 thru 79 has the amounts.
    The key variables to find are in col C & D rows 83 & 84 and 87 & 88.
    The formula is just adding no matter what the the variables are. in column M I placed the correct Amounts in RED.

    Any thoughts on what I am doing incorrectly.

    Thanks
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Summing with 2 variables (XP & 2003)

    You should probably look at the SUMPRODUCT() function.

    The formula in K83 would be:
    =SUMPRODUCT(($G$9:$G$78=D83)*($C$9:$C$78=C83)*$L$9 :$L$78)

    Your totals in red for the IID section are incorrect.

    I've reattached the spreadsheet with the formulas.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Conditional Summing with 2 variables (XP & 2003)

    Thank you. I will check out the sum product formula. It also looks alot less painstaking that the sum if.

    I still don't know why the sumif didn't work. Any down and dirty ideas.

    Thanks again.

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Summing with 2 variables (XP & 2003)

    Any down and dirty ideas. - umm the formula is wrong.

    Please don't take offense - I have never tried to use this kind of array formula. To me, using the array formula is over complicating the method needed.

    I've tried to dissect the formula, but I haven't had any luck.

    I've figured out with the help of CPEARSON.com

    The formula in L83 should be:
    {=SUM(IF((($C$13:$C$79=$C83)+($G$13:$G$79=$D83))>1 ,$L$13:$L$79,0))}
    instead of:
    {=SUM($C$13:$C$79=$C84,IF(G13:$G$79=$D84,L13:$L$79 ,0),0)}

    The array formula does basically the same as the formula I supplied, but is harder to maintain. The formula takes each column and compares it to a value. If you have more than 2 variables you have to change the ">1" to ">?" where the "?" is one less than your variable count. With a sumproduct you can keep taking on the variable checks. One advantage, I just thought of, is that if you want to check 3 columns but only needed 2 of the variables to match you could use the >1 in the formula.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Summing with 2 variables (XP & 2003)

    I don't see how using the Array construction of SUM(IF(... is any more complicated than SUMPRODUCT. The only advantage I see in the SUMPRODUCT is that it is implicitly an array formula so that it does not require using ctrl-shift-enter to confirm. The SUM(IF(... is not implicitly an array so one must explicitly tell excel it is an array. In both you are setting up the criteria with one or more comparisons strung together by adding (to indicate an OR) or by using multiplication (to indicate an AND).

    But the SUM(IF(... type of construction is much more versatile. SUMPRODUCT only Sums so it can be used to total or even to count if setup. The SUM(IF(.. type of contstruction can be used to get any of the statisitical functions to determine the average, min, max, varaiance, etc with multiple criteria. It can even be used to determine Medians, ranks, and even the min and max rows/columns with particular data...

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Summing with 2 variables (XP & 2003)

    Steve,

    Quoting myself "To me, using the array formula is over complicating the method needed." The OP wanted to compare two columns and add the data from a third. Perfect job for a simple sumproduct().

    Maybe I should have I have said "but is, in my opinion harder to maintain.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Summing with 2 variables (XP & 200

    Hi, MNN ,

    Your formula missing one (1) IF function after the SUM function

    The revision was in :

    1] Your original formula in cell L83

    {=SUM($C$13:$C$79=$C83,IF(G13:$G$79=$D83,L13:$L$79 ,0),0)}

    2] The revised formula :

    {=SUM(IF($C$13:$C$79=$C83,IF(G13:$G$79=$D83,L13:$L $79,0),0))}

    Regards
    Bosco

  8. #8
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Conditional Summing with 2 variables (XP & 200

    SMACK! I could have had a V8.

    Thanks, I guess I couldn't see the forest because of the trees.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •