Sumifs using unique and filter functions

[Tarek78]

Hi Everyone,

I have attached a spreadsheet and highlighted in yellow the column K I would need to be filled with a formula (spill formula only so that it is completely automated).

Column I and J: This is a spill formula using Unique, Filter and Choose function.
Column K: I would need a spill formula to get the sum of the sales generated by each rep for each region covered


I tried a lot to work on it but cannot find an answer

Thanks for your help

 

[Alex Blakenburg]

I would have thought this would give you the same result but with the pivot doing most of the work and giving you added capabilities.

PS: I have stacked the Filter function on top of the Pivot's output.

20220605 Filter Unique Sort or Pivot Tarek78.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	IRIS CODE	Project	Commercial Contract	Sales			IRIS CODE	Commercial Contract	Project	Sales
2	ACCO	FR.1001	TARS	1000			SNCF	MISTRAL	FR.0400	25000
3	ACCO	FR.1002	Not Assigned	500			SNCF	Not Assigned	FR.1111	3000
4	ACCO	FR.1001	TARS	2000			RATP	RAE	FR.1122	20000
5	ACCO	FR.1003	FRI	4000			ACCO	FRI	FR.1003	4000
6	SNCF	FR.0400	MISTRAL	10000			ACCO	TARS	FR.1001	3000
7	SNCF	FR.0400	MISTRAL	15000			ACCO	Not Assigned	FR.1002	500
8	SNCF	FR.1111	Not Assigned	3000
9	RATP	FR.1122	RAE	20000
10
11	Stacked on top of Pivot Columns
12
13	IRIS CODE	Commercial Contract	Project	Sales
14	SNCF	MISTRAL	FR.0400	25000
15	SNCF	Not Assigned	FR.1111	3000
16	RATP	RAE	FR.1122	20000
17	ACCO	FRI	FR.1003	4000
18	ACCO	TARS	FR.1001	3000
19	ACCO	Not Assigned	FR.1002	500
20
Data

Cell Formulas
Range	Formula
A13:D19	A13	=FILTER($G:$J,$G:$G<>"")
Dynamic array formulas.

 

[Tarek78]

Hi Alex, I think you are right. It is much easier to build a pivot, sort it, and then use a filter function based on this pivot.

I would like to thank you all.

 

[Alex Blakenburg]

We appreciate the feedback. Glad we could help.

 

[Fonzie]

Sure, a variation of that formula would work:

Book1
	A	B	C	D	E	F
1	Sales Rep	Region	Sales
2	Simon	East	1000
3	Simon	West	2000
4	Simon	North	1000		Rep	Total Sales
5	Tim	South	4000		Tim	10000
6	Tim	East	5000		Nick	5000
7	Tim	West	1000		Simon	4000
8	Nick	South	4000
9	Nick	West	1000
Sheet5

Cell Formulas
Range	Formula
E5:F7	E5	=LET(r,UNIQUE(Table1_2[Sales Rep]),SORT(CHOOSE({1,2},r,SUMIFS(Table1_2[Sales],Table1_2[Sales Rep],r)),2,-1))
Dynamic array formulas.

Hi there - if the table had a fourth column, let's say 'Returns', can the formula be modified to include that one as well?

 

[Eric W]

Hi there - if the table had a fourth column, let's say 'Returns', can the formula be modified to include that one as well?

Like this?

Book1
	A	B	C	D	E	F	G	H
1	Sales Rep	Region	Sales	Returns
2	Simon	East	1000	10
3	Simon	West	2000	20
4	Simon	North	1000	30		Rep	Total Sales	Returns
5	Tim	South	4000	40		Tim	10000	150
6	Tim	East	5000	50		Nick	5000	150
7	Tim	West	1000	60		Simon	4000	60
8	Nick	South	4000	70
9	Nick	West	1000	80
Sheet1

Cell Formulas
Range	Formula
F5:H7	F5	=LET(r,UNIQUE(Table1_2[Sales Rep]),SORT(CHOOSE({1,2,3},r,SUMIFS(Table1_2[Sales],Table1_2[Sales Rep],r),SUMIFS(Table1_2[Returns],Table1_2[Sales Rep],r)),2,-1))
Dynamic array formulas.