Sales Apportionment for Sellers

[deciog]

Gentlemen

I came across one more problem, formula where the value of the sale is divided between two sellers
Follow the example worksheet. It's possible?

Decio

 

[vletm]

... only for two sellers!

 

[r2c2]

Gentlemen

I came across one more problem, formula where the value of the sale is divided between two sellers
Follow the example worksheet. It's possible?

Decio

Interesting question (although questionable data structure)

@vletm : cool solution

Here is a formula that works for any number of people, assuming:

• the names are unique
• the separator is /
• One name doesn't contain another name (ie one person is named Aline and another named Caline)

=SUMPRODUCT(IFERROR(ISNUMBER(FIND(D2,$B$2:$B$15)),0) / ((LEN($B$2:$B$15)-LEN(SUBSTITUTE($B$2:$B$15,"/","")))+1), $A$2:$A$15)

CTRL+Shift+Enter this formula.

We just check how many cells have the name in D2 and for each cell. This gives a bunch of 0s and 1s. We then divide this with the number of times / is present in that cell. We add + 1 to the number of times (since if a sale belongs to one person then / won't be present in that cell). We then multiply this new array with actual sales to apportion the sales.

Hope that helps.

 

[deciog]

vletm, Thanks for answering

Yes, each sale may have two different sellers

Due to the time zone, I took time to respond, I'm from Brazil São Paulo

Thank you your solution worked perfectly



r2c2, Thanks too for the answer

It worked perfectly too, and a great solution, I liked it for more sellers

Thank you all for the answer and solution.

Thank you very much

Decio

 

[bosco_yip]

If only 2 sellers, another possible formula :

=SUMIF(B:B,D2,A:A)+SUMIF(B:B,"*/*",A:A)/2

Regards
Bosco

 

[deciog]

Bosco,

Very good, not matrix formula

I'll use this

Thank you so much.

Decio

 

[deciog]

Apologies for coming back on the same subject, after placing in the company appeared names with equal initials that I explain attached spreadsheet.

No more surprises, thanks in advance, thanks.

Decio

 

[John Jairo V]

Hi @deciog

Check a slight change from @r2c2 formulae. Blessings!

 

[vletm]

@deciog
Original: I came across one more problem, formula where the value of the sale is divided between two sellers
... so soon there will be more sellers, Not only those original two,
who wants to join to party ...
So, I did sample how to do this with more than two sellers...

 

[deciog]

John Jairo V

Spectacular, our very good

Thank you again

Decio

 

[deciog]

vletm

Thanks also for giving a solution, but in the company does not let use macro

Thanks also

Decio