SUMIFS on unique rows

[mimi_nyc]

Hello,
I'm working a commission report template and running into duplicate values issue. We calculate commission based on the $ value of deals closed by sales reps, and the sales manager get commission on total deal values of their direct reports. Sometimes two members of the same team share a deal so each sales rep will get commission on that deal, however their sales manager should only get credit ONCE. I use SUMIFS because I have to look at multiple criteria before rolling up the revenue (like sales name, time period, etc), but when I do SUMIFS, excel rolls up the revenue twice from both reps. the desired outcome is to count that deal only once. We have unique contract #s that we can use to identify dupes. Attached sample data on gdrive . Please help

SAMPLE DATA - SUMIFS

 

[6StringJazzer]

What are the values that uniquely identify one deal? (If this were a database, I would be asking you what is the key.) All the rows here have the same contract number, so that's probably not it. Does the Period determine the deal? If so, it looks like the first three rows are all the same deal, with three partners and two managers.

Can you elaborate more on your business model?

 

[mimi_nyc]

What are the values that uniquely identify one deal? (If this were a database, I would be asking you what is the key.) All the rows here have the same contract number, so that's probably not it. Does the Period determine the deal? If so, it looks like the first three rows are all the same deal, with three partners and two managers.

Can you elaborate more on your business model?

Hi! we're in advertising business, so the closed deals are advertising campaign that run over time, say 3 months but we recognize revenue monthly based on the time period between start of advertising campaign to end of campaign. in this case, the looney campaign total was $150K, with $100K allocated in 2024. the key/unique identifier is the contract number. In this case, sales manager should be paid once for 2024 for that same contract # for where he is listed as the manager. he is now getting paid x2 because two of his members share the same contract.
In a larger set, he would have more reps rolling to him, and multiple opportunities thats why I added the manager as identifier to sumup all of the direct reports revenue. I hope that makes sense...I didnt finish my coffee today

 

[mimi_nyc]

Hello,
I'm working a commission report template and running into duplicate values issue. We calculate commission based on the $ value of deals closed by sales reps, and the sales manager get commission on total deal values of their direct reports. Sometimes two members of the same team share a deal so each sales rep will get commission on that deal, however their sales manager should only get credit ONCE. I use SUMIFS because I have to look at multiple criteria before rolling up the revenue (like sales name, time period, etc), but when I do SUMIFS, excel rolls up the revenue twice from both reps. the desired outcome is to count that deal only once. We have unique contract #s that we can use to identify dupes. Attached sample data on gdrive . Please help

SAMPLE DATA - SUMIFS

View attachment 105375

Is it possible to do SUMIFs and add a criteria to only some the contract + period once? i.e sum if contract is unique and manager = Mr Peanut, Period = 2024 and

 

[mimi_nyc]

Hi! we're in advertising business, so the closed deals are advertising campaign that run over time, say 3 months but we recognize revenue monthly based on the time period between start of advertising campaign to end of campaign. in this case, the looney campaign total was $150K, with $100K allocated in 2024. the key/unique identifier is the contract number. In this case, sales manager should be paid once for 2024 for that same contract # for where he is listed as the manager. he is now getting paid x2 because two of his members share the same contract.
In a larger set, he would have more reps rolling to him, and multiple opportunities thats why I added the manager as identifier to sumup all of the direct reports revenue. I hope that makes sense...I didnt finish my coffee today

@6StringJazzer sorry forgot to tag you to my response. I'm new to this forum

 

[Sam_D_Ben]

Based on your data, is this what you're after?

Book1
	A	B	C	D	E	F	G
1	Share Partner	Manager	Opportunity Name	Contract #	Period	Year	Revenue
2	Mickey Mouse	Mr Peanut	Looney Tunes Browdway 2004	-	-	2024	$100,000
3	Minnie Mouse	Mr Peanut	Looney Tunes Browdway 2004	-	-	2024	$100,000
4	Duffy Duck	Scooby Doo	Looney Tunes Browdway 2004	-	-	2024	$100,000
5	Mickey Mouse	Mr Peanut	Looney Tunes Browdway 2004	-	-	2023	$50,000
6	Minnie Mouse	Mr Peanut	Looney Tunes Browdway 2004	-	-	2023	$50,000
7	Duffy Duck	Scooby Doo	Looney Tunes Browdway 2004	-	-	2023	$50,000
8
9
10	Name	Period	Booked Revenue
11	Mickey Mouse	2024	$100,000
12	Minnie Mouse	2024	$100,000
13	Duffy Duck	2024	$100,000
14	Mr Peanut	2024	$200,000
15	Scooby Doo	2024	$100,000
Sheet1

Cell Formulas
Range	Formula
C11:C15	C11	=SUMPRODUCT(--($A$2:$A$7=$A11)--($B$2:$B$7=$A11),--($F$2:$F$7=B11),$G$2:$G$7)

Thanks

 

[Peter_SSs]

.. or is it this?

24 01 20.xlsm
	A	B	C	D	E	F	G
1	Share Partner	Manager	Opportunity Name	Contract #	Period	Year	Revenue
2	Mickey Mouse	Mr Peanut	Looney Tunes Browdway 2004	1182024	-	2024	100,000.00
3	Minnie Mouse	Mr Peanut	Looney Tunes Browdway 2004	1182024	-	2024	100,000.00
4	Duffy Duck	Scooby Doo	Looney Tunes Browdway 2004	1182024	-	2024	100,000.00
5	Mickey Mouse	Mr Peanut	Looney Tunes Browdway 2004	1182024	-	2023	50,000.00
6	Minnie Mouse	Mr Peanut	Looney Tunes Browdway 2004	1182024	-	2023	50,000.00
7	Duffy Duck	Scooby Doo	Looney Tunes Browdway 2004	1182024	-	2023	50,000.00
8
9
10	Name	Period	Booked Revenue
11	Mickey Mouse	2024	100,000.00
12	Minnie Mouse	2024	100,000.00
13	Duffy Duck	2024	100,000.00
14	Mr Peanut	2024	100,000.00
15	Scooby Doo	2024	100,000.00
Revenue

Cell Formulas
Range	Formula
C11:C15	C11	=SUM(TAKE(UNIQUE(FILTER(D$2:G$7,((B$2:B$7=A11)+(A$2:A$7=A11))*(F$2:F$7=B11))),,-1))

 

[mimi_nyc]

.. or is it this?

24 01 20.xlsm
	A	B	C	D	E	F	G
1	Share Partner	Manager	Opportunity Name	Contract #	Period	Year	Revenue
2	Mickey Mouse	Mr Peanut	Looney Tunes Browdway 2004	1182024	-	2024	100,000.00
3	Minnie Mouse	Mr Peanut	Looney Tunes Browdway 2004	1182024	-	2024	100,000.00
4	Duffy Duck	Scooby Doo	Looney Tunes Browdway 2004	1182024	-	2024	100,000.00
5	Mickey Mouse	Mr Peanut	Looney Tunes Browdway 2004	1182024	-	2023	50,000.00
6	Minnie Mouse	Mr Peanut	Looney Tunes Browdway 2004	1182024	-	2023	50,000.00
7	Duffy Duck	Scooby Doo	Looney Tunes Browdway 2004	1182024	-	2023	50,000.00
8
9
10	Name	Period	Booked Revenue
11	Mickey Mouse	2024	100,000.00
12	Minnie Mouse	2024	100,000.00
13	Duffy Duck	2024	100,000.00
14	Mr Peanut	2024	100,000.00
15	Scooby Doo	2024	100,000.00
Revenue

Cell Formulas
Range	Formula
C11:C15	C11	=SUM(TAKE(UNIQUE(FILTER(D$2:G$7,((B$2:B$7=A11)+(A$2:A$7=A11))*(F$2:F$7=B11))),,-1))

amazing! thank you so much @Peter_SSs this is what I was looking for! I'll test it out with my full scenario but thats the correct total revenue!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.