Count Unique Customers Who Order Using Formula

excelmania370

New Member
Joined
Mar 22, 2016
Messages
7
Hi All,

I'm trying to work out how many unique customers bought a particular product. Example Below
ProductOrdersUnique Buyers of Order
Bouncy Castle56? (Formula Required)
Trampoline32?
(Formula Required

<tbody>
</tbody>
Inflatable Pool77?
(Formula Required)

<tbody>
</tbody>

<tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

excelmania370

New Member
Joined
Mar 22, 2016
Messages
7
Sure, here's some mock data (can't put real data for obvious reasons)

User IDSectorProductQuantityOrder Date
324RetailTrampoline1703-Jan-17
544AutomotiveTrampoline304-Feb-17
324RetailTrampoline5431-Jan-17
907CateringBouncy Castle2207-Jul-17
217RetailPool1821-Nov-17
907CateringBouncy Castle10601-Oct-17
123AutomotiveTrampoline4705-Aug-17

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1User IDSectorProductQuantityOrder Date
2324RetailTrampoline173-Jan-17
3544AutomotiveTrampoline34-Feb-17
4324RetailTrampoline5431-Jan-17
5907CateringBouncy Castle227-Jul-17
6217RetailPool1821-Nov-17
7907CateringBouncy Castle1061-Oct-17
8123AutomotiveTrampoline475-Aug-17
9
10
11
12ProductJan-2017Feb-2017
13Trampoline11
14Bouncy Castle00
15Pool00
16
17Sector2017
18Retail2
19Automotive2
20Catering1
21
22
23SectorProduct2017
24RetailTrampoline1
25RetailPool1
26AutomotiveTrampoline2
27CateringBouncy Castle1
Sheet
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634

ADVERTISEMENT

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

ABCDEFGH
1User IDSectorProductQuantityOrder Date TrampolineUniqe customer of Trampoline in January
2 jan
3324RetailTrampoline17jan 3242
4544AutomotiveTrampoline3feb
5324RetailTrampoline54jan 324
6907CateringBouncy Castle22jul
7217RetailTrampoline18jan 217
8907CateringBouncy Castle106oct
9123AutomotiveTrampoline47aug

<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>
</tbody>

Spreadsheet Formulas
CellFormula
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
 
Last edited:

excelmania370

New Member
Joined
Mar 22, 2016
Messages
7
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1User IDSectorProductQuantityOrder Date
2324RetailTrampoline173-Jan-17
3544AutomotiveTrampoline34-Feb-17
4324RetailTrampoline5431-Jan-17
5907CateringBouncy Castle227-Jul-17
6217RetailPool1821-Nov-17
7907CateringBouncy Castle1061-Oct-17
8123AutomotiveTrampoline475-Aug-17
9
10
11Jan-2017Feb-2017Mar-2017Apr-2017
12Trampoline1100
13Bouncy Castle0000
14Pool0000
Sheet
 

excelmania370

New Member
Joined
Mar 22, 2016
Messages
7
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,181
Messages
5,594,720
Members
413,926
Latest member
andycap

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top