Formula to Count Unique Distinct Values That Meet Multiple Criteria

[Gomesy]

Working on another project for work where I need to look at over 25K rows of data and perform a count of the Unique Order Numbers that made up our Web Orders in each of the last 4 years. My file has 2 tabs. One is labeled "Sales History" and the other is called "Metrics". The "Metrics" tab is where all of my information needs to summarize.

I tried using an array combination of Sumif and Countifs formulas but can't quite seem to get it to work.

Here is a sampling of my data found on the "Sales History" tab:


[TABLE="width: 182"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Order #[/TD]
[TD]Year[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]117524122[/TD]
[TD]2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]117661819[/TD]
[TD]2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]118358926[/TD]
[TD]2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]118682343[/TD]
[TD]2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]118700680[/TD]
[TD]2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]119543065[/TD]
[TD]2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]119920626[/TD]
[TD]2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]121099398[/TD]
[TD]2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]121099398[/TD]
[TD]2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]121099398[/TD]
[TD]2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]121099398[/TD]
[TD]2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]121099398[/TD]
[TD]2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]121099398[/TD]
[TD]2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]121099398[/TD]
[TD]2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123782132[/TD]
[TD]2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]127465017[/TD]
[TD]2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]302163150[/TD]
[TD]2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]302163150[/TD]
[TD]2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]302460485[/TD]
[TD]2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]502324656[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]502326151[/TD]
[TD]2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]502410851[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]502416849[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]502460540[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]502463371[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]502562568[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]502576243[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]502625989[/TD]
[TD]2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]503172589[/TD]
[TD]2016[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]503725656[/TD]
[TD]2017[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]503816214[/TD]
[TD]2017[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]503816214[/TD]
[TD]2017[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]W106260323 [/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]W106260323[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]W106260323[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]W106260323[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]W106260323[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]W106260323[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]W106260323[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]W106260323[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]W106260323[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]W106260323[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]W106260323[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]W106260323[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]W106260323[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]W106260323[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
[TR]
[TD]W106260323[/TD]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[/TR]
</tbody>[/TABLE]

Can someone help me please?

 

[AhoyNC]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Change ranges to match your data and drag formula down as needed.

Excel Workbook
	A	B
1	Year	Count Web Orders
2	2014	8
3	2015	0
4	2016	1
5	2017	2
Metrics

Excel Workbook
	A	B	C
1	Order #	Year	Web Orders
2	117524122	2014
3	117661819	2014
4	118358926	2014
5	118682343	2014
6	118700680	2014
7	119543065	2014
8	119920626	2015
9	121099398	2015
10	121099398	2015
11	121099398	2015
12	121099398	2015
13	121099398	2015
14	121099398	2015
15	121099398	2015
16	123782132	2016
17	127465017	2017
18	302163150	2015
19	302163150	2015
20	302460485	2015
21	502324656	2014	Web Orders
22	502326151	2014
23	502410851	2014	Web Orders
24	502416849	2014	Web Orders
25	502460540	2014	Web Orders
26	502463371	2014	Web Orders
27	502562568	2014	Web Orders
28	502576243	2014	Web Orders
29	502625989	2015
30	503172589	2016	Web Orders
31	503725656	2017	Web Orders
32	503816214	2017	Web Orders
33	503816214	2017	Web Orders
34	W106260323	2014	Web Orders
35	W106260323	2014	Web Orders
36	W106260323	2014	Web Orders
37	W106260323	2014	Web Orders
38	W106260323	2014	Web Orders
39	W106260323	2014	Web Orders
40	W106260323	2014	Web Orders
41	W106260323	2014	Web Orders
42	W106260323	2014	Web Orders
43	W106260323	2014	Web Orders
44	W106260323	2014	Web Orders
45	W106260323	2014	Web Orders
46	W106260323	2014	Web Orders
47	W106260323	2014	Web Orders
48	W106260323	2014	Web Orders
Sales History

 

[Eric W]

Maybe:

Excel 2012
	E	F	G	H
1	2014	2015	2016	2017
2	8	0	1	2
Sheet1

Cell Formulas
Range	Formula
E2	{=SUM(SIGN(FREQUENCY(IF($B$2:$B$48=E1,IF($C$2:$C$48="Web Orders",MATCH($A$2:$A$48,$A$2:$A$48,0))),ROW($A$2:$A$48)-ROW($A$2)+1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

I was born in Savannah, but I haven't been back in many, many years!

 

[Gomesy]

This one worked great! Thank you so much. I have another challenge. I added a couple more fields to the database. This time I need to find the Unique and Distinct # of units (guest rooms) that correspond to Unique Customers who placed a Web Order in 2014. Same two worksheets as before. I tried just adding the additional fields of customer # and units to the equation but didn't get a valid answer. How would you tweak the formula you gave me to solve this puzzle?

[TABLE="width: 234"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Year[/TD]
[TD]Web Orders[/TD]
[TD]HDS Account #[/TD]
[TD]Units[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD]12560316[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD]12560316[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD]4887227[/TD]
[TD]79[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD]4887227[/TD]
[TD]79[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[TD]1939643[/TD]
[TD]94[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[TD]1939643[/TD]
[TD]94[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[TD]1939643[/TD]
[TD]94[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[TD]1939643[/TD]
[TD]94[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[TD]1939643[/TD]
[TD]94[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD]5909972[/TD]
[TD]112[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD]5909972[/TD]
[TD]112[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD]2068909[/TD]
[TD]86[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]3329331[/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]3329331[/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]3329331[/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]3329331[/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]3329331[/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Web Orders[/TD]
[TD]6129968[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Web Orders[/TD]
[TD]6129968[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]6151214[/TD]
[TD]115[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]6151214[/TD]
[TD]115[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]6151214[/TD]
[TD]115[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]6147656[/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]6147656[/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD][/TD]
[TD]2066665[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD][/TD]
[TD]2066665[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD][/TD]
[TD]2066665[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]Web Orders[/TD]
[TD]14148293 [/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD][/TD]
[TD]14354110[/TD]
[TD]78[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD][/TD]
[TD]14354110[/TD]
[TD]78[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD][/TD]
[TD]14354110[/TD]
[TD]78[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD][/TD]
[TD]14354110[/TD]
[TD]78[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD][/TD]
[TD]12560316[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD][/TD]
[TD]12560316[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD][/TD]
[TD]12560316[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD][/TD]
[TD]12560316[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD][/TD]
[TD]12560316[/TD]
[TD]80[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!

 

[Aladin Akyurek]

@Gomesy, post #4

What is the expected count?

 

[Gomesy]

94 units for 2014

 

[Gomesy]

Wow Eric! This one works too. We just moved to Savannah from Chicago 3 months ago. We live on one of the barrier islands (Skidaway). It's like a little piece of heaven. Thanks for responding to my request. Maybe you'd care to take a look at my 2nd request. It just adds another search element than the first one.

Thanks for helping me out!

Rick

 

[Aladin Akyurek]

94 units for 2014

94 units, that is, 94 unique/distinct items? Maybe you are intending to obtain a list and that list consists of 94, a single result?

 

[Gomesy]

Yes 94 is the single result I am looking for. If you look at the data you will see that there is only one customer that placed Web Orders in 2014 and that one customer's hotel has 94 units (rooms). My data set has 25K rows of data. A formula to extract this information would be helpful.

Thank you,

Rick

 

[Aladin Akyurek]

Yes 94 is the single result I am looking for. If you look at the data you will see that there is only one customer that placed Web Orders in 2014 and that one customer's hotel has 94 units (rooms). My data set has 25K rows of data. A formula to extract this information would be helpful.

Thank you,

Rick

A2:A48 is named as Year, B2:B48 as WebOrders, C2:C48 as Account, and D2:D48 as Units in what follows.

In G1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(Year=2014,IF(WebOrders="web orders",IF(ISNUMBER(MATCH(Account,IF(Year=2014,IF(WebOrders="web orders",Account)),0)),Units))),Units),1))

In G2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($G$2:G2)>$G$1,"",INDEX(Units,SMALL(IF(FREQUENCY(IF(Year=2014,IF(WebOrders="web orders",IF(ISNUMBER(MATCH(Account,IF(Year=2014,IF(WebOrders="web orders",Account)),0)),MATCH(Units,Units,0)))),ROW(Units)-ROW(INDEX(Units,1,1))+1),ROW(Units)-ROW(INDEX(Units,1,1))+1),ROWS($G$2:G2))))