Results 1 to 6 of 6
Thread: conditional summing (2003 sp2)

20070314, 17:54 #1
 Join Date
 Nov 2001
 Posts
 40
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?
thanks in advance.

20070314, 18:09 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20070314, 19:06 #3
 Join Date
 Nov 2001
 Posts
 40
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20070314, 20:24 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20070314, 20:51 #5
 Join Date
 Nov 2001
 Posts
 40
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20070314, 21:02 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: conditional summing (2003 sp2)
You can use TRUE=1 and FALSE=0 for many powerful formula techniques.