# Thread: Conditional Summing with 2 variables (XP & 2003)

1. ## 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

2. ## 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.

3. ## 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. ## 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))}
{=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. ## 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. ## 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. ## 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. ## 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
•