Results 1 to 4 of 4
Thread: conditional sum (2003 sp1)

20051030, 00:15 #1
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: conditional sum (2003 sp1)
<pre>=SUMPRODUCT((A1:A3=C1:C3)*1,B1:B3)</pre>
Steve

20051030, 07:12 #2
 Join Date
 Jul 2001
 Posts
 132
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: conditional sum (2003 sp1)
WOW, that was so simple and works great, but I don;t really understand what it is doing and how it owrks, I read the help but it is brutal when it comes to explaining ranges like that. The *1 is to convert to number from text right?

20051030, 08:47 #3
 Join Date
 Jul 2001
 Posts
 132
 Thanks
 0
 Thanked 0 Times in 0 Posts
conditional sum (2003 sp1)
Edited by HansV to insert <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags around table to preserve spaces.
I want to do something I would think is quite simple but can't see to figure out. I have a bunch of stuff in column a,b,c...Now here is what I want to do
Suppose I have this...
<pre> A B C
1 YES 4 YES
2 YES 5 NO
3 NO 6 NO
</pre>
I want to sum up the middle row based on if the columns A and C Match. So in this case, since A1 = C1 and A3 = C3, the answer I would get = 10 (the 4 + 6)
Suppose whatever formula I use will be in D1
Now I have a lot of rows so using multiple if statements is not an option. I also do not want to use another row to do something like IF(A1=C1,B1,0) and then add them all up, I was hoping to use some kind of simple yet elegant array formula.
Thanks for the help

20051030, 08:53 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: conditional sum (2003 sp1)
The result of A1:A3=C1:C3 is a series of TRUE/FALSE values. Multiplying this by 1 forces Excel to interpret these values as numbers: TRUE = 1 and FALSE = 0.
In your example, A1:A3=C1:C3 rsults in {TRUE,FALSE,TRUE}. The *1 converts this to {1,0,1}. So the sumproduct multiplies {1,0,1} with {4,5,6}, i.e.1*4 + 0*5 + 1*6, which is exactly what you wanted.