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

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)

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)

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

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