# Thread: conditional summing (2003 sp2)

1. ## conditional summing (2003 sp2)

HI there

I have a list of credit card transactions.

I need to total that amounts paid by card where

transaction code (column [img]/forums/images/smilies/cool.gif[/img] =1
AND
transaction description code (column I) = 1
AND
Card type (column J) = "amex" or "visa" or "m/c"

Sumif will work for one condition. I was just reading about sumproduct and how it can do conditional summing, but got lost.

Can anyone help with a formula?

2. ## Re: conditional summing (2003 sp2)

You can use
<code>
=SUMPRODUCT((B2:B1000=1)*(I2:I1000=1)*(J2:J1000="a mex")*P2:P1000)
</code>
to sum the amounts in column P for which the corresponding cell in column B is 1, that in column I is 1 too and that in column J is "amex". You can create similar formulas for "visa" and "Mc" and add the results together. You must, of course, adjust the ranges.

3. ## Re: conditional summing (2003 sp2)

thanks Hans - that works great.

Now I just have to understand why it works so I can teach others who will use the sheet I am making.

4. ## Re: conditional summing (2003 sp2)

Let's take a simplified example:

<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>1</td><td align=center>Card Type</td><td align=center>Amount</td><td align=center>2</td><td>visa</td><td align=right>100</td><td align=center>3</td><td>amex</td><td align=right>50</td><td align=center>4</td><td>visa</td><td align=right>150</td><td align=center>5</td><td>mc</td><td align=right>75</td><td align=center>6</td><td>amex</td><td align=right>125</td></table>
In the formula

=SUMPRODUCT((A2:A6="amex")*B2:B6)

A2:A6="amex" returns {FALSE,TRUE,FALSE,FALSE,TRUE} since only the second and last cells equal "amex".
In Excel, FALSE is equivalent to 0 and TRUE is equivalent to 1, so {FALSE,TRUE,FALSE,FALSE,TRUE} is equivalent to {0,1,0,0,1}
The formula is evaluated as

=SUMPRODUCT({0,1,0,0,1}*{100,50,150,75,125})

SUMPRODUCT multiplies the first element of the arguments, then multiplies the second element of the arguments, etc. and finally adds the results together.
So the formula calculates 0*100 + 1*50 + 0*150 + 0*75 + 1*125. As you see, only the amex amounts will be counted, the others disappear because they are multiplied by 0.

5. ## Re: conditional summing (2003 sp2)

light bulb - bing bing bing

I was not connecting the true = 1 and false = 0 so could not see how the multiplication worked.

I get it now!!!!

Another excellent formula in Excel

thanks again

6. ## Re: conditional summing (2003 sp2)

You can use TRUE=1 and FALSE=0 for many powerful formula techniques.

#### Posting Permissions

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