matching a customer with a twist..

[Julian DosSantos]

Hello.. need some help. I need to compile a 'sumifs' formula to add sales for the salesmen.

Below is an example table. My dilemma is determining that in addition to the sales I only want to 'sum' the 'CANCEL' sale of a customer that initially purchased a 'GOOD' Status and not a 'PENDING' status.

How can i write a formula to determine that (for example) Customer3 'cancelled' the 'pending' sale, therefore I do not want the cancel to sum in my calculations.



Date Salesman Customer Product Status Sale
Jan 8th John Customer1 Apples Good $500
Jan 9th Mark Customer2 Pears Good $200
Jan 8th Kevin Customer3 Oranges Pending $250
Jan 15th Mark Customer2 Pears Cancel $200
Jan 16th Kevin Customer3 Oranges Cancel $250

Thank you so much!!
Julian

 

[isabelle]

hi Julian,

i'm not sure i understand your asks,

but maybe we can try in this way

=SUM(F2:F6)-SUMPRODUCT(--(C2:C6="Customer3")*(D26="Oranges")*(E2:E6="Cancel")*(F2:F6))


isabelle

Le 2013-08-31 17:27, Julian DosSantos a écrit :

Hello.. need some help. I need to compile a 'sumifs' formula to add sales for the salesmen.

Below is an example table. My dilemma is determining that in addition to the sales

I only want to 'sum' the 'CANCEL' sale of a customer that initially
purchased a 'GOOD' Status and not a 'PENDING' status.

How can i write a formula to determine that (for example) Customer3 'cancelled' the 'pending' sale, therefore

I do not want the cancel to sum in my calculations.