If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Specific function to get the sum
Hi all,
I have an excel table with data like: ticket amount invoice n° 45 145$ 7025.222-(75 ) 45 120$ 7025.222-(75) 45 12$ 7025.222-(25) 46 555$ 7025.235 46 10$ 7025.236 47 188$ 7026.735-(80) 47 52$ 7026.735-(20) What I'd like to do is: to make the sum of each ticket "like 45" but with an argument when the invoice ends with -(75) make sum of ticket 45 = [(145$+120$)/75%],if invoice ends with -(25) ignores to include in sum. and when the invoice ends without brackets "like ticket 46"; make sum [555$+10$], and when invoice ends with -(80) make sum of ticket "like 47" = [(188$+52$)/80%] and ignore to include invoices end with -(20) in this sum. Could any body help. Thanks Lassaad |
#2
|
|||
|
|||
Specific function to get the sum
wrote in message
... Hi all, I have an excel table with data like: ticket amount invoice n° 45 145$ 7025.222-(75 ) 45 120$ 7025.222-(75) 45 12$ 7025.222-(25) 46 555$ 7025.235 46 10$ 7025.236 47 188$ 7026.735-(80) 47 52$ 7026.735-(20) What I'd like to do is: to make the sum of each ticket "like 45" but with an argument when the invoice ends with -(75) make sum of ticket 45 = [(145$+120$)/75%],if invoice ends with -(25) ignores to include in sum. and when the invoice ends without brackets "like ticket 46"; make sum [555$+10$], and when invoice ends with -(80) make sum of ticket "like 47" = [(188$+52$)/80%] and ignore to include invoices end with -(20) in this sum. Could any body help. Thanks Lassaad Try something like =SUMPRODUCT(--(A2:A8=45),--(RIGHT(C2:C8,5)="-(75)"),B2:B8) |
#3
|
|||
|
|||
Specific function to get the sum
=SUMPRODUCT(--(ticket=45),--(RIGHT(invoice,5)="-(75)"),amount)/75%
=SUMPRODUCT(--(ticket=46),--(ISNUMBER(invoice)),amount) =SUMPRODUCT((ticket=47)*(RIGHT(invoice,5)={"-(80)","-(20)"})*amount)/80% "Stephen" wrote: wrote in message ... Hi all, I have an excel table with data like: ticket amount invoice n° 45 145$ 7025.222-(75 ) 45 120$ 7025.222-(75) 45 12$ 7025.222-(25) 46 555$ 7025.235 46 10$ 7025.236 47 188$ 7026.735-(80) 47 52$ 7026.735-(20) What I'd like to do is: to make the sum of each ticket "like 45" but with an argument when the invoice ends with -(75) make sum of ticket 45 = [(145$+120$)/75%],if invoice ends with -(25) ignores to include in sum. and when the invoice ends without brackets "like ticket 46"; make sum [555$+10$], and when invoice ends with -(80) make sum of ticket "like 47" = [(188$+52$)/80%] and ignore to include invoices end with -(20) in this sum. Could any body help. Thanks Lassaad Try something like =SUMPRODUCT(--(A2:A8=45),--(RIGHT(C2:C8,5)="-(75)"),B2:B8) |
#4
|
|||
|
|||
Specific function to get the sum
Thanks for all of you.
How if I have 1852 lines with the same topic as in my post. ticket amount invoice n° Duplicated frequently Duplicated frequently appreciate your assistance Regards |
#5
|
|||
|
|||
Specific function to get the sum
How if I have 1852 lines with the same topic as in my post!
ticket : Duplicated frequently amount invoice n° : Duplicated frequently appreciate your assistance Thanks |
Thread Tools | |
Display Modes | |
|
|