| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.access, microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.access,microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.access
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions, microsoft.public.access
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions, microsoft.public.access
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| What function do I use to return a certain value for specific text | Excel Discussion (Misc queries) | |||
| specific date function | Excel Worksheet Functions | |||
| How do I perform a contains function for a specific cell? | Excel Worksheet Functions | |||
| Lookup Function - Specific Values | Excel Worksheet Functions | |||
| How can I use an IF function to look for specific cell formatting. | Excel Worksheet Functions | |||
Linear Mode
