# Thread: conditional sum (2003 sp1)

1. ## Re: conditional sum (2003 sp1)

<pre>=SUMPRODUCT((A1:A3=C1:C3)*1,B1:B3)</pre>

Steve

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

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

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

#### Posting Permissions

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