Counting number of unique values in a column

[jimithing007]

I'm having a hard time finding a resolution unique to my situation here.

I am compiling a sales report and I need to determine exactly how many orders my sales people are selling. This report must contain every line item sold, and each order can contain one to many line items. So I have one tab titled "Rep Orders" that looks like this:

On my other tab called "Sales by Rep", I need a formula for the B column that looks at the Order# column above, and calculates the total of unique order numbers per each rep (named in the C column above):

Thanks in advance!

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post some sample data.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jimithing007]

Thanks for the info. Account details updated. I'm using Office365.

Here is the Rep Orders tab:

Rep Network Orders - MrExcel.xlsx
	A	B	C	D
1	Date	Order#	Rep	Line Item Sold
2	1/1/2020	1001	John	Product 1
3	1/1/2020	1001	John	Product 2
4	1/1/2020	1001	John	Product 3
5	1/1/2020	1001	John	Product 4
6	1/1/2020	1002	John	Product 1
7	1/1/2020	1002	John	Product 2
8	1/1/2020	1002	John	Product 3
9	1/1/2020	1002	John	Product 4
10	1/1/2020	1002	John	Product 5
11	1/1/2020	1002	John	Product 6
12	1/2/2020	1003	Mike	Product 2
13	1/3/2020	1004	Sue	Product 7
14	1/4/2020	1004	Sue	Product 8
15	1/4/2020	1004	Sue	Product 9
16	1/4/2020	1004	Sue	Product 10
17	1/4/2020	1004	Sue	Product 11
18	1/4/2020	1004	Sue	Product 12
19	1/4/2020	1004	Sue	Product 13
20	1/4/2020	1004	Sue	Product 14
21	1/4/2020	1004	Sue	Product 15
22	1/4/2020	1004	Sue	Product 16
23	1/5/2020	1005	David	Product 1
24	1/6/2020	1006	Lily	Product 3
25	1/6/2020	1006	Lily	Product 4
26	1/7/2020	1007	Ryan	Product 5
27	1/7/2020	1007	Ryan	Product 6
28	1/7/2020	1007	Ryan	Product 7
Rep Orders

And here is my other tab called "Sales by Rep", here's where I need a formula for the B column that looks at the Order# column above, and calculates the total of unique order numbers per each rep (named in the C column above):

Rep Network Orders - MrExcel.xlsx
	A	B
1	Rep	No. of Orders sold
2	John
3	Mike
4	Sue
5	David
6	Lily
7	Ryan
Sales by Rep

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B
1	Rep	No. of Orders sold
2	John	2
3	Mike	1
4	Sue	1
5	David	1
6	Lily	1
7	Ryan	1
Stock

Cell Formulas
Range	Formula
B2:B7	B2	=ROWS(UNIQUE(FILTER('Rep Orders'!$B$2:$B$50,'Rep Orders'!$C$2:$C$50=A2)))

 

[jimithing007]

Worked like a charm. Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[jimithing007]

Is there a way to add to this formula so that it looks at the date, and only calculates what's in that date range? 2019 sales only, for example.

also, I'm looking for a formula that only calculates the sum in a given date range.

Rep Orders tab:

Rep Network Orders - MrExcel.xlsx
	A	B	C	D	E
1	Date	Order#	Rep	Line Item Sold	Order Amount
2	1/1/2018	1001	John	Product 1	$1,000.00
3	1/1/2018	1001	John	Product 2	$1,500.00
4	1/1/2018	1001	John	Product 3	$150.00
5	1/1/2018	1001	John	Product 4	$20.00
6	1/1/2018	1002	John	Product 1	$3,000.00
7	1/1/2018	1002	John	Product 2	$100,000.00
8	1/1/2018	1002	John	Product 3	$2,567.00
9	1/1/2019	1002	John	Product 4	$40.00
10	1/1/2019	1002	John	Product 5	$1.00
11	1/1/2019	1002	John	Product 6	$40.00
12	1/1/2019	1003	Mike	Product 2	$20.00
13	1/1/2019	1004	Sue	Product 7	$200.00
14	1/1/2019	1004	Sue	Product 8	$600.00
15	1/1/2019	1004	Sue	Product 9	$777.00
16	1/1/2020	1004	Sue	Product 10	$789.00
17	1/1/2020	1004	Sue	Product 11	$1,000.00
18	1/1/2020	1004	Sue	Product 12	$111.00
19	1/1/2020	1004	Sue	Product 13	$33.00
20	1/1/2020	1004	Sue	Product 14	$201.00
21	1/1/2020	1004	Sue	Product 15	$2,104.00
22	1/1/2020	1004	Sue	Product 16	$14,230.00
23	1/5/2020	1005	David	Product 1	$2,342.00
24	1/6/2020	1006	Lily	Product 3	$10,123.00
25	1/6/2020	1006	Lily	Product 4	$120.00
26	1/7/2020	1007	Ryan	Product 5	$31,231.00
27	1/7/2020	1007	Ryan	Product 6	$12.00
28	1/7/2020	1007	Ryan	Product 7	$31.00
Rep Orders

Sales by Rep tab:

Rep Network Orders - MrExcel.xlsx
	A	B	C
1	Rep	Total Sales $ for 2019	No. of Orders sold in 2019
2	John		2
3	Mike		1
4	Sue		1
5	David		1
6	Lily		1
7	Ryan		1
Sales by Rep

Cell Formulas
Range	Formula
C2:C7	C2	=ROWS(UNIQUE(FILTER('Rep Orders'!$B$2:$B$50,'Rep Orders'!$C$2:$C$50=A2)))

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1	Rep	Total Sales $ for 2019	No. of Orders sold in 2019
2	John	81	1
3	Mike	20	1
4	Sue	1577	1
5	David	0
6	Lily	0
7	Ryan	0
Summary

Cell Formulas
Range	Formula
B2:B7	B2	=SUM(FILTER('Rep orders'!$E$2:$E$50,('Rep orders'!$C$2:$C$50=A2)*(YEAR('Rep orders'!$A$2:$A$50)=2019),0))
C2:C7	C2	=IFERROR(ROWS(UNIQUE(FILTER('Rep orders'!$B$2:$B$50,('Rep orders'!$C$2:$C$50=A2)*(YEAR('Rep orders'!$A$2:$A$50)=2019)))),"")

 

[jimithing007]

Works great! Is there a way to modify the formula so that it looks at the entire column instead of the range (C2:C50, A2:A50, etc.)? This report is likely to have many rows.

 

[Fluff]

It's best to avoid using whole column references as they can bring your workbook to a crawl.
Just change the 50 to a row number that will always be enough to cover your data (without going OTT)