sum with multipel ifs
 

this is my excel layout. i want a total of the amount field where
description ends with "R" or where description ends with IV OR if it
ends with "R IVY". i tried this =SUM(IF(B2:B40=RIGHT("R"),)*C2:C40) it
gave me 0.00


date description amount
6/10/05 Deposit IV ($961.00)
3/16/05 Deposit M ($5,000.00)
4/20/05 Deposit M ($25,000.00)
6/8/05 Deposit M ($6,077.72)
6/30/05 Deposit M ($4,171.70)
5/20/05 Deposit R ($100.00)
6/1/05 Deposit R ($30.00)
6/13/05 DEPOSIT R ($4,096.00)
6/20/05 DEPOSIT R ($2,340.00)
6/24/05 DEPOSIT R ($1,780.00)
6/27/05 DEPOSIT R ($2,210.00)
6/30/05 DEPOSIT R ($3,300.00)
7/5/05 DEPOSIT R ($4,844.00)
7/7/05 DEPOSIT R ($5,261.19)
7/19/05 DEPOSIT R ($3,155.00)
7/20/05 DEPOSIT R ($835.00)
7/20/05 DEPOSIT R ($812.05)
7/21/05 DEPOSIT R ($130.00)
7/12/05 DEPOSIT R IVY ($3,856.75)

sum with multipel ifs
 

Hi!

Try this:

J1 = R
J2 = IV
J3 = R IVY

=SUMPRODUCT(--(RIGHT(B2:B20,1)=J1)+(RIGHT(B2:B20,2)=J2)+(RIGHT(B 2:B20,5)=J3),C2:C20)

Biff

wrote in message
oups.com...
this is my excel layout. i want a total of the amount field where
description ends with "R" or where description ends with IV OR if it
ends with "R IVY". i tried this =SUM(IF(B2:B40=RIGHT("R"),)*C2:C40) it
gave me 0.00


date description amount
6/10/05 Deposit IV ($961.00)
3/16/05 Deposit M ($5,000.00)
4/20/05 Deposit M ($25,000.00)
6/8/05 Deposit M ($6,077.72)
6/30/05 Deposit M ($4,171.70)
5/20/05 Deposit R ($100.00)
6/1/05 Deposit R ($30.00)
6/13/05 DEPOSIT R ($4,096.00)
6/20/05 DEPOSIT R ($2,340.00)
6/24/05 DEPOSIT R ($1,780.00)
6/27/05 DEPOSIT R ($2,210.00)
6/30/05 DEPOSIT R ($3,300.00)
7/5/05 DEPOSIT R ($4,844.00)
7/7/05 DEPOSIT R ($5,261.19)
7/19/05 DEPOSIT R ($3,155.00)
7/20/05 DEPOSIT R ($835.00)
7/20/05 DEPOSIT R ($812.05)
7/21/05 DEPOSIT R ($130.00)
7/12/05 DEPOSIT R IVY ($3,856.75)

sum with multipel ifs
 

I would opt for the easy way and use a helper column (column E) and in
E2 put

=IF(RIGHT(B2,5)="R
IVY","Y",IF(RIGHT(B2,2)="IV","V",IF(RIGHT(B2,1)="R ","R","")))

and formula copy this to the end of your data in B

then use either 3 sum figures:

=SUMIF(E$2:E$20,"Y",C$2:C$20)

=SUMIF(E$2:E$20,"V",C$2:C$20)

=SUMIF(E$2:E$20,"R",C$2:C$20)

or use 'R' three times in the E column,

=IF(RIGHT(B2,5)="R
IVY","R",IF(RIGHT(B2,2)="IV","R",IF(RIGHT(B2,1)="R ","R","")))

and just one Sum ='R' if you only need one total.

note, your range was 2:40, but I tested 2:20

Hope this helps


Wrote:
this is my excel layout. i want a total of the amount field where
description ends with "R" or where description ends with IV OR if it
ends with "R IVY". i tried this =SUM(IF(B2:B40=RIGHT("R"),)*C2:C40) it
gave me 0.00


date description amount
6/10/05 Deposit IV ($961.00)
3/16/05 Deposit M ($5,000.00)
4/20/05 Deposit M ($25,000.00)
6/8/05 Deposit M ($6,077.72)
6/30/05 Deposit M ($4,171.70)
5/20/05 Deposit R ($100.00)
6/1/05 Deposit R ($30.00)
6/13/05 DEPOSIT R ($4,096.00)
6/20/05 DEPOSIT R ($2,340.00)
6/24/05 DEPOSIT R ($1,780.00)
6/27/05 DEPOSIT R ($2,210.00)
6/30/05 DEPOSIT R ($3,300.00)
7/5/05 DEPOSIT R ($4,844.00)
7/7/05 DEPOSIT R ($5,261.19)
7/19/05 DEPOSIT R ($3,155.00)
7/20/05 DEPOSIT R ($835.00)
7/20/05 DEPOSIT R ($812.05)
7/21/05 DEPOSIT R ($130.00)
7/12/05 DEPOSIT R IVY ($3,856.75)


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=476695