Results 1 to 8 of 8

20080904, 11:31 #1
 Join Date
 Jun 2005
 Posts
 390
 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

20080904, 14:21 #2
 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.

20080904, 14:38 #3
 Join Date
 Jun 2005
 Posts
 390
 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.

20080904, 15:54 #4
 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.

20080905, 00:32 #5
 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 ctrlshiftenter 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

20080905, 02:20 #6
 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.

20080906, 20:11 #7
 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

20080906, 22:17 #8
 Join Date
 Jun 2005
 Posts
 390
 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.