Formula to Count Unique Distinct Values That Meet Multiple Criteria

G

Gomesy

New Member
Joined
Oct 16, 2005
Messages
31
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:


Order #YearWeb Orders
1175241222014
1176618192014
1183589262014
1186823432014
1187006802014
1195430652014
1199206262015
1210993982015
1210993982015
1210993982015
1210993982015
1210993982015
1210993982015
1210993982015
1237821322016
1274650172017
3021631502015
3021631502015
3024604852015
5023246562014Web Orders
5023261512014
5024108512014Web Orders
5024168492014Web Orders
5024605402014Web Orders
5024633712014Web Orders
5025625682014Web Orders
5025762432014Web Orders
5026259892015
5031725892016Web Orders
5037256562017Web Orders
5038162142017Web Orders
5038162142017Web Orders
W106260323 2014Web Orders
W1062603232014Web Orders
W1062603232014Web Orders
W1062603232014Web Orders
W1062603232014Web Orders
W1062603232014Web Orders
W1062603232014Web Orders
W1062603232014Web Orders
W1062603232014Web Orders
W1062603232014Web Orders
W1062603232014Web Orders
W1062603232014Web Orders
W1062603232014Web Orders
W1062603232014Web Orders
W1062603232014Web Orders

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

Can someone help me please?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
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
AB
1YearCount Web Orders
220148
320150
420161
520172
Metrics
Excel Workbook
ABC
1Order #YearWeb Orders
21175241222014
31176618192014
41183589262014
51186823432014
61187006802014
71195430652014
81199206262015
91210993982015
101210993982015
111210993982015
121210993982015
131210993982015
141210993982015
151210993982015
161237821322016
171274650172017
183021631502015
193021631502015
203024604852015
215023246562014Web Orders
225023261512014
235024108512014Web Orders
245024168492014Web Orders
255024605402014Web Orders
265024633712014Web Orders
275025625682014Web Orders
285025762432014Web Orders
295026259892015
305031725892016Web Orders
315037256562017Web Orders
325038162142017Web Orders
335038162142017Web Orders
34W1062603232014Web Orders
35W1062603232014Web Orders
36W1062603232014Web Orders
37W1062603232014Web Orders
38W1062603232014Web Orders
39W1062603232014Web Orders
40W1062603232014Web Orders
41W1062603232014Web Orders
42W1062603232014Web Orders
43W1062603232014Web Orders
44W1062603232014Web Orders
45W1062603232014Web Orders
46W1062603232014Web Orders
47W1062603232014Web Orders
48W1062603232014Web Orders
Sales History
 
Upvote 0
Maybe:


Excel 2012
EFGH
12014201520162017
28012
Sheet1
Cell Formulas
RangeFormula
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!
 
Upvote 0
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?

YearWeb OrdersHDS Account #Units
20141256031680
20141256031680
2014488722779
2014488722779
2014Web Orders193964394
2014Web Orders193964394
2014Web Orders193964394
2014Web Orders193964394
2014Web Orders193964394
20145909972112
20145909972112
2014206890986
2015332933183
2015332933183
2015332933183
2015332933183
2015332933183
2015Web Orders612996885
2015Web Orders612996885
20156151214115
20156151214115
20156151214115
2015614765683
2015614765683
2016206666568
2016206666568
2016206666568
2016Web Orders14148293106
2016Web Orders14148293106
2016Web Orders14148293106
2016Web Orders14148293 106
2016Web Orders14148293106
20161435411078
20161435411078
20161435411078
20161435411078
2017Web Orders14148293106
2017Web Orders14148293106
2017Web Orders14148293106
2017Web Orders14148293106
2017Web Orders14148293106
2017Web Orders14148293106
20171256031680
20171256031680
20171256031680
20171256031680
20171256031680

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

Thanks!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Gomesy said:
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
Click to expand...

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))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Formula to Count Unique Distinct Values That Meet Multiple Criteria - Phase 2
Replies
8
Views
636
Gomesy
G

Forum statistics

Threads
1,216,099
Messages
6,128,820
Members
449,469
Latest member
Kingwi11y

Share this page

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
Back
Top