Count Unique items based on criteria

[trenteb]

I have a set of sales data by date by location and by customer. I want to be able to count the customers who have purchased by month by location, whether they have purchased multiple times during the month or just once doesn't matter they should only be counted once as trying to ascertain the number of active customers.

Note - not able to post mini sheet, not being provided with option?

 

[alansidman]

Cannot manipulate data in a picture. Please reload to this site using XL2BB so that we don't have to retype your data. Additionally, cannot read your data as it is all black and very indistinguishable.

 

[trenteb]

would love to however - only being provided option to upload an image. I'm sure user error/ignorance at play here - if you have any insight as to what I need to do to get the option to load mini sheet they are welcome.

 

[alansidman]

XL2BB - Excel Range to BBCode

smozgur submitted a new Excel Add-in: XL2BB - Excel Range to BBCode - Convert your Excel range to BBCode Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without...

www.mrexcel.com

 

[trenteb]

Not sure this is what you are after, it is a copy and paste of the Mini sheet data

Samplecount1.xlsx
	B	C	D
4	Month	Sales Office	Customer Name
5	30/11/2023	ROC	Customer A
6	20/11/2023	ROC	Customer A
7	30/11/2023	ABC	Customer C
8	30/11/2023	SHE	Customer E
9	30/11/2023	SHE	Customer F
10	31/10/2023	ROC	Customer A
11	31/10/2023	ABC	Customer C
12	31/10/2023	ABC	Customer D
13	31/10/2023	SHE	Customer E
14	20/10/2023	SHE	Customer F
15	12/10/2023	SHE	Customer F
16	30/09/2023	ROC	Customer A
17	20/09/2023	ROC	Customer A
18	30/09/2023	ROC	Customer B
19	30/09/2023	ABC	Customer C
20	27/09/2023	ABC	Customer C
21	12/09/2023	ABC	Customer C
22	30/09/2023	SHE	Customer E
23	12/09/2023	SHE	Customer E
24	31/08/2023	ROC	Customer B
25	31/08/2023	ABC	Customer C
26	31/08/2023	ABC	Customer D
27	31/08/2023	SHE	Customer E
28	12/08/2023	SHE	Customer E
29	10/08/2023	SHE	Customer E
30	31/08/2023	SHE	Customer F
31	31/07/2023	ABC	Customer C
32	31/07/2023	ABC	Customer C
33	31/07/2023	ABC	Customer C
34	31/07/2023	ABC	Customer D
35	31/07/2023	SHE	Customer G
36	31/07/2023	SHE	Customer F
37	12/07/2023	SHE	Customer F
38	7/07/2023	SHE	Customer F
39	31/07/2023	SHE	Customer E
Sheet1

 

[Peter_SSs]

Not sure this is what you are after,

Yes, that is it exactly. BTW, I had mentioned XL2BB and given you a link to it a day or so ago in your other thread.

Welcome to the MrExcel board!

For the future, it would help greatly if you provided sample data (& expected results) with XL2BB so helpers don't have to manually type out sample data to test with & show results.

See if this is what you want

trenteb.xlsm
	B	C	D	E	F	G	H	I
4	Month	Sales Office	Customer Name			ABC	ROC	SHE
5	30/11/2023	ROC	Customer A		Jul-23	2	0	3
6	20/11/2023	ROC	Customer A		Aug-23	2	1	2
7	30/11/2023	ABC	Customer C		Sep-23	1	2	1
8	30/11/2023	SHE	Customer E		Oct-23	2	1	2
9	30/11/2023	SHE	Customer F		Nov-23	1	1	2
10	31/10/2023	ROC	Customer A
11	31/10/2023	ABC	Customer C
12	31/10/2023	ABC	Customer D
13	31/10/2023	SHE	Customer E
14	20/10/2023	SHE	Customer F
15	12/10/2023	SHE	Customer F
16	30/09/2023	ROC	Customer A
17	20/09/2023	ROC	Customer A
18	30/09/2023	ROC	Customer B
19	30/09/2023	ABC	Customer C
20	27/09/2023	ABC	Customer C
21	12/09/2023	ABC	Customer C
22	30/09/2023	SHE	Customer E
23	12/09/2023	SHE	Customer E
24	31/08/2023	ROC	Customer B
25	31/08/2023	ABC	Customer C
26	31/08/2023	ABC	Customer D
27	31/08/2023	SHE	Customer E
28	12/08/2023	SHE	Customer E
29	10/08/2023	SHE	Customer E
30	31/08/2023	SHE	Customer F
31	31/07/2023	ABC	Customer C
32	31/07/2023	ABC	Customer C
33	31/07/2023	ABC	Customer C
34	31/07/2023	ABC	Customer D
35	31/07/2023	SHE	Customer G
36	31/07/2023	SHE	Customer F
37	12/07/2023	SHE	Customer F
38	7/07/2023	SHE	Customer F
39	31/07/2023	SHE	Customer E
Sheet1

Cell Formulas
Range	Formula
G4:I4	G4	=TRANSPOSE(UNIQUE(SORT(C5:C39)))
F5:F9	F5	=UNIQUE(TEXT(SORT(B5:B39),"mmm-yy"))
G5:I9	G5	=IFNA(ROWS(UNIQUE(FILTER($D$5:$D$39,(TEXT($B$5:$B$39,"mmm-yy")=$F5)*($C$5:$C$39=G$4),NA()))),0)
Dynamic array formulas.

 

[trenteb]

worked a treat - thank you kindly. Can you assist if I added another criteria ie. how many unique customers have purchased in a category by location? see data below

Copy of Samplecount2.xlsx
	B	C	D	E
4	Month	Sales Office	Category	Customer Name
5	30/11/2023	ROC	Black	Customer A
6	20/11/2023	ROC	Black	Customer A
7	30/11/2023	ABC	Green	Customer C
8	30/11/2023	SHE	Green	Customer E
9	30/11/2023	SHE	Black	Customer F
10	31/10/2023	ROC	Yellow	Customer A
11	31/10/2023	ABC	Black	Customer C
12	31/10/2023	ABC	Green	Customer D
13	31/10/2023	SHE	Yellow	Customer E
14	20/10/2023	SHE	Yellow	Customer F
15	12/10/2023	SHE	Green	Customer F
16	30/09/2023	ROC	Black	Customer A
17	20/09/2023	ROC	Yellow	Customer A
18	30/09/2023	ROC	Yellow	Customer B
19	30/09/2023	ABC	Yellow	Customer C
20	27/09/2023	ABC	Green	Customer C
21	12/09/2023	ABC	Green	Customer C
22	30/09/2023	SHE	Green	Customer E
23	12/09/2023	SHE	Black	Customer E
24	31/08/2023	ROC	Black	Customer B
25	31/08/2023	ABC	Black	Customer C
26	31/08/2023	ABC	Green	Customer D
27	31/08/2023	SHE	Green	Customer E
28	12/08/2023	SHE	Green	Customer E
29	10/08/2023	SHE	Yellow	Customer E
30	31/08/2023	SHE	Yellow	Customer F
31	31/07/2023	ABC	Green	Customer C
32	31/07/2023	ABC	Green	Customer C
33	31/07/2023	ABC	Green	Customer C
34	31/07/2023	ABC	Yellow	Customer D
35	31/07/2023	SHE	Yellow	Customer G
36	31/07/2023	SHE	Black	Customer F
37	12/07/2023	SHE	Black	Customer F
38	7/07/2023	SHE	Green	Customer F
39	31/07/2023	SHE	Green	Customer E
Sheet1

 

[Peter_SSs]

how many unique customers have purchased in a category by location?

.. by month?

 

[trenteb]

yes please

 

[Peter_SSs]

Could we add a helper column?

trenteb.xlsm
	B	C	D	E	F	G	H	I	J	K
4	Month	mmm-yy	Sales Office	Category	Customer Name
5	30/11/2023	Nov-23	ROC	Black	Customer A		Jul-23	SHE	Green	2
6	20/11/2023	Nov-23	ROC	Black	Customer A		Jul-23	SHE	Black	1
7	30/11/2023	Nov-23	ABC	Green	Customer C		Jul-23	ABC	Green	1
8	30/11/2023	Nov-23	SHE	Green	Customer E		Jul-23	ABC	Yellow	1
9	30/11/2023	Nov-23	SHE	Black	Customer F		Jul-23	SHE	Yellow	1
10	31/10/2023	Oct-23	ROC	Yellow	Customer A		Aug-23	SHE	Yellow	2
11	31/10/2023	Oct-23	ABC	Black	Customer C		Aug-23	SHE	Green	1
12	31/10/2023	Oct-23	ABC	Green	Customer D		Aug-23	ROC	Black	1
13	31/10/2023	Oct-23	SHE	Yellow	Customer E		Aug-23	ABC	Black	1
14	20/10/2023	Oct-23	SHE	Yellow	Customer F		Aug-23	ABC	Green	1
15	12/10/2023	Oct-23	SHE	Green	Customer F		Sep-23	ABC	Green	1
16	30/09/2023	Sep-23	ROC	Black	Customer A		Sep-23	SHE	Black	1
17	20/09/2023	Sep-23	ROC	Yellow	Customer A		Sep-23	ROC	Yellow	2
18	30/09/2023	Sep-23	ROC	Yellow	Customer B		Sep-23	ROC	Black	1
19	30/09/2023	Sep-23	ABC	Yellow	Customer C		Sep-23	ABC	Yellow	1
20	27/09/2023	Sep-23	ABC	Green	Customer C		Sep-23	SHE	Green	1
21	12/09/2023	Sep-23	ABC	Green	Customer C		Oct-23	SHE	Green	1
22	30/09/2023	Sep-23	SHE	Green	Customer E		Oct-23	SHE	Yellow	2
23	12/09/2023	Sep-23	SHE	Black	Customer E		Oct-23	ROC	Yellow	1
24	31/08/2023	Aug-23	ROC	Black	Customer B		Oct-23	ABC	Black	1
25	31/08/2023	Aug-23	ABC	Black	Customer C		Oct-23	ABC	Green	1
26	31/08/2023	Aug-23	ABC	Green	Customer D		Nov-23	ROC	Black	1
27	31/08/2023	Aug-23	SHE	Green	Customer E		Nov-23	ABC	Green	1
28	12/08/2023	Aug-23	SHE	Green	Customer E		Nov-23	SHE	Green	1
29	10/08/2023	Aug-23	SHE	Yellow	Customer E		Nov-23	SHE	Black	1
30	31/08/2023	Aug-23	SHE	Yellow	Customer F
31	31/07/2023	Jul-23	ABC	Green	Customer C
32	31/07/2023	Jul-23	ABC	Green	Customer C
33	31/07/2023	Jul-23	ABC	Green	Customer C
34	31/07/2023	Jul-23	ABC	Yellow	Customer D
35	31/07/2023	Jul-23	SHE	Yellow	Customer G
36	31/07/2023	Jul-23	SHE	Black	Customer F
37	12/07/2023	Jul-23	SHE	Black	Customer F
38	7/07/2023	Jul-23	SHE	Green	Customer F
39	31/07/2023	Jul-23	SHE	Green	Customer E
Sheet2

Cell Formulas
Range	Formula
H5:J29	H5	=UNIQUE(SORTBY(C5:E39,B5:B39))
K5:K29	K5	=ROWS(UNIQUE(FILTER(F$5:F$39,(C$5:C$39=H5)*(D$5:D$39=I5)*(E$5:E$39=J5))))
C5:C39	C5	=TEXT(B5,"mmm-yy")
Dynamic array formulas.