conditional summing (2003 sp2)

Topic

New Reply

HI there

I have a list of credit card transactions.

I need to total that amounts paid by card where

transaction code (column =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.

Reply | Quote

Replies

You can use

=SUMPRODUCT((B2:B1000=1)*(I2:I1000=1)*(J2:J1000="amex")*P2:P1000)

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.

Reply | Quote

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.

Reply | Quote

Let’s take a simplified example:

	A	B
1	Card Type	Amount
2	visa	100
3	amex	50
4	visa	150
5	mc	75
6	amex	125

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote