Count Unique Customers Who Order Using Formula

[excelmania370]

Hi All,

I'm trying to work out how many unique customers bought a particular product. Example Below
[TABLE="width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Orders[/TD]
[TD]Unique Buyers of Order[/TD]
[/TR]
[TR]
[TD]Bouncy Castle[/TD]
[TD]56[/TD]
[TD]? (Formula Required)[/TD]
[/TR]
[TR]
[TD]Trampoline[/TD]
[TD]32[/TD]
[TD]? [TABLE="width: 500"]
<tbody>[TR]
[TD](Formula Required[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Inflatable Pool[/TD]
[TD]77[/TD]
[TD]? [TABLE="width: 500"]
<tbody>[TR]
[TD](Formula Required)[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The raw data I have is the product, quantity sold, when it was sold, customer type and the customer ID. I need working out the number of unique purchasers by product by month, which I've been doing so far through a long winded method of putting the data into a Pivot table and then a COUNT of orders each userID had made. But I also need a formula to work out the unique purchasers by Customer Type so e.g. How many unique purchasers from the Automotive Sector, and then Retail Sector etc. etc. ordered the 3 products above (Doesn't have to be by month). As an FYI, if a customer ordered twice in a month, that would be 1 unique purchaser.

Any more info needed I'll be happy to provide.

Thanks in Advance

 

[István Hirsch]

You should present some of the raw data along with the desired result created manually.

 

[excelmania370]

Sure, here's some mock data (can't put real data for obvious reasons)

[TABLE="width: 378"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]User ID[/TD]
[TD]Sector[/TD]
[TD]Product[/TD]
[TD]Quantity[/TD]
[TD]Order Date[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]324[/TD]
[TD]Retail[/TD]
[TD]Trampoline[/TD]
[TD]17[/TD]
[TD]03-Jan-17[/TD]
[/TR]
[TR]
[TD]544[/TD]
[TD]Automotive[/TD]
[TD]Trampoline[/TD]
[TD]3[/TD]
[TD]04-Feb-17[/TD]
[/TR]
[TR]
[TD]324[/TD]
[TD]Retail[/TD]
[TD]Trampoline[/TD]
[TD]54[/TD]
[TD]31-Jan-17[/TD]
[/TR]
[TR]
[TD]907[/TD]
[TD]Catering[/TD]
[TD]Bouncy Castle[/TD]
[TD]22[/TD]
[TD]07-Jul-17[/TD]
[/TR]
[TR]
[TD]217[/TD]
[TD]Retail[/TD]
[TD]Pool[/TD]
[TD]18[/TD]
[TD]21-Nov-17[/TD]
[/TR]
[TR]
[TD]907[/TD]
[TD]Catering[/TD]
[TD]Bouncy Castle[/TD]
[TD]106[/TD]
[TD]01-Oct-17[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Automotive[/TD]
[TD]Trampoline[/TD]
[TD]47[/TD]
[TD]05-Aug-17[/TD]
[/TR]
</tbody>[/TABLE]

So for my first issue, I could do a sumif to show that 71 Trampolines were bought in Jan-17, I could do a countif to show there were 2 orders of trampolines in Jan-17 but can't work out a formula that it's 1 unique customer that bought trampolines in Jan-17.

For my second issue, I could do a sumif to show the retail sector bought 89 products, but again, can't find a formula to show that 2 unique users from Retail had ordered. To dive deeper I would need an extension of the formula to display that 1 unique user bought Trampolines from the retail sector and 1 unique user bought a Pool from the Retail sector.

In real life, whatever formula would span 10,000+ rows of data.

Hope this makes sense & thanks

 

[AhoyNC]

Maybe something like this:
These are array formulas and must be entered with CTRL-SHIFT-ENTER.
Drag formulas down (and across for months) as needed.

In the example below cells B12 and C12 are actual dates (1/1/17 and 2/1/17) formatted as mmm-yyy.
The year in B17 and C23 is just the number 2017.

Excel Workbook
	A	B	C	D	E
1	User ID	Sector	Product	Quantity	Order Date
2	324	Retail	Trampoline	17	3-Jan-17
3	544	Automotive	Trampoline	3	4-Feb-17
4	324	Retail	Trampoline	54	31-Jan-17
5	907	Catering	Bouncy Castle	22	7-Jul-17
6	217	Retail	Pool	18	21-Nov-17
7	907	Catering	Bouncy Castle	106	1-Oct-17
8	123	Automotive	Trampoline	47	5-Aug-17
9
10
11
12	Product	Jan-2017	Feb-2017
13	Trampoline	1	1
14	Bouncy Castle	0	0
15	Pool	0	0
16
17	Sector	2017
18	Retail	2
19	Automotive	2
20	Catering	1
21
22
23	Sector	Product	2017
24	Retail	Trampoline	1
25	Retail	Pool	1
26	Automotive	Trampoline	2
27	Catering	Bouncy Castle	1
Sheet

 

[István Hirsch]

Try this:

Enter the product to evaluate in cell G1, the order date in G2. Then enter the formula in G3 (and copy down) and in H3. The number of unique customers is displayed in H3. You can find the answer to the other two questions in a similar way.

Sheet1

	A	B	C	D	E	F	G	H
User ID	Sector	Product	Quantity	Order Date		Trampoline	Uniqe customer of Trampoline in January
						jan
Retail	Trampoline	jan
Automotive	Trampoline	feb
Retail	Trampoline	jan
Catering	Bouncy Castle	jul
Retail	Trampoline	jan
Catering	Bouncy Castle	oct
Automotive	Trampoline	aug

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:70px;"><col style="width:105px;"><col style="width:94px;"><col style="width:64px;"><col style="width:84px;"><col style="width:31px;"><col style="width:91px;"><col style="width:122px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]324[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"]324[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]544[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]324[/TD]

[TD="align: right"]54[/TD]

[TD="align: right"]324[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]907[/TD]

[TD="align: right"]22[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]217[/TD]

[TD="align: right"]18[/TD]

[TD="align: right"]217[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]907[/TD]

[TD="align: right"]106[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"]47[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G3 =IF(AND(C3=$G$1,E3=$G$2),A3,"") H3 =SUMPRODUCT(($G$3:$G$9<>"")/COUNTIF($G$3:$G$9,$G$3:$G$9&""&"")) G4 =IF(AND(C4=$G$1,E4=$G$2),A4,"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[excelmania370]

Absolutely genius! Thanks for that. Unfortunately the formula takes ages to calculate (even on my brand new pc) and setting the formulas to manual won't help much either. This was, however, exactly what I was looking for.

Thanks again

 

[AhoyNC]

Shouldn't the unique count for Trampoline be 1 for Jan. (same customer same sector).
On a large data base the SUMPRODUCT and COUNTIF formulas are going to be slow. The frequency formula should be faster (but still may take awhile).
The dates oin row 11 of the example are actual dates (1/1/17, 2/1/17 etc) formatted as mmm-yyyy

This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down and across as needed (and change ranges to match your data).

Excel Workbook
	A	B	C	D	E
1	User ID	Sector	Product	Quantity	Order Date
2	324	Retail	Trampoline	17	3-Jan-17
3	544	Automotive	Trampoline	3	4-Feb-17
4	324	Retail	Trampoline	54	31-Jan-17
5	907	Catering	Bouncy Castle	22	7-Jul-17
6	217	Retail	Pool	18	21-Nov-17
7	907	Catering	Bouncy Castle	106	1-Oct-17
8	123	Automotive	Trampoline	47	5-Aug-17
9
10
11		Jan-2017	Feb-2017	Mar-2017	Apr-2017
12	Trampoline	1	1	0	0
13	Bouncy Castle	0	0	0	0
14	Pool	0	0	0	0
Sheet

 

[excelmania370]

Yes, in the case of the trampoline orders in Jan-17, it's the 1 unique user that I need, as opposed to the 2 (orders) I would get if I done a countifs. This is exactly what I was trying to get but my computer does not like it at all so I may revert it to when I just need it on a month-by-month basis.

Thanks!