Results 1 to 6 of 6
  1. #1
    Lounger
    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.

  2. #2
    Plutonium Lounger
    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.

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

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

  5. #5
    Lounger
    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

  6. #6
    Plutonium Lounger
    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.

Posting Permissions

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