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?
 
Sort by date Sort by votes
I just ended up changing the 1 at the end to Units to determine how many actual units I had as opposed to how many unique customers had placed Web Orders in 2014. Otherwise the formula in G1 works PERFECTLY!!!!

Thank you so much! This is a BIG ONE to solve for me.
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Gomesy said:
I just ended up changing the 1 at the end to Units to determine how many actual units I had as opposed to how many unique customers had placed Web Orders in 2014. Otherwise the formula in G1 works PERFECTLY!!!!

Thank you so much! This is a BIG ONE to solve for me.
Click to expand...

You are welcome.
 
Upvote 0
Hi Aladin! I meant to write you a few days ago but I found a problem with the formula that appears in G1. It obviously worked perfectly in the sample set I had given you because my sample data didn't consider something that my real-life database (of 25K rows) had, namely, duplicate unit counts. Here's the problem; if one account # placed a Web Order in 2014 and had a unit count of 94 then the formula should return a result of 94 (which it does) however, if a different account # also placed a Web Order in 2014 and also had a unit count of 94 (since these are not necessarily unique values), the formula does not work since it only counts the 94 units once. The formula omits duplicates of unit counts which it should not be doing when a different account # had the same amount of units.

Is there any way to fix this problem please?

Thanks a million!

Rick
 
Upvote 0
YearWeb OrdersHDS Account #Units
2014Web Orders12560316 94
201412560316 94
2014Web Orders488722779
2014488722779
2014Web Orders193964394
2014Web Orders193964394
2014Web Orders193964394
2014Web Orders193964394
2014Web Orders193964394
2014Web Orders5909972112
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 Orders14148293106
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>
 
Upvote 0
What is the desired output? Note that the current set up yields a count of 3 and a list consisting of 94, 79, and 112.
 
Upvote 0
Desired output is 94 + 79 + +94 + 112 for a total of 379. This total represents the number of units in each unique account #. The account #'s only need to be tallied once in a year if the account # in question placed one or more Web Orders. My goal is to take the total spend per account # and divide it by the unique units to determine the spend per unit on Web Orders. If the number of units for a unique account # is tallied more than once during a year the spend per unit will be under reported.

Thank you.
 
Upvote 0
Gomesy said:
Desired output is 94 + 79 + +94 + 112 for a total of 379. This total represents the number of units in each unique account #. The account #'s only need to be tallied once in a year if the account # in question placed one or more Web Orders. My goal is to take the total spend per account # and divide it by the unique units to determine the spend per unit on Web Orders. If the number of units for a unique account # is tallied more than once during a year the spend per unit will be under reported.

Thank you.
Click to expand...

In G1 control+shift+enter:

=SUM(IF(FREQUENCY(IF(Year=2014,IF(WebOrders="web orders",Account)),Account),1))

G2: List

In G3 control+shift+enter and copy down:

=IF(ROWS($G$3:G3)>$G$1,"",INDEX(Units,SMALL(IF(FREQUENCY(IF(Year=2014,IF(WebOrders="web orders",MATCH(Account,Account,0))),ROW(Account)-ROW(INDEX(Account,1,1))+1),ROW(Account)-ROW(INDEX(Account,1,1))+1),ROWS($G$3:G3))))
 
Upvote 0
I got the formula in G1 to return a value of 4 (which I am assuming means the # of accounts that met the criteria in your formula). 4 is indeed the number of accounts that placed web orders at least once in 2014.

I don't understand what you mean when you write the word " List " for the value that is supposed to appear in G2. Can you explain please?

I copied and pasted your formula into G3 and got a value of 94. The desired output is 379. When you look at the 4 accounts that met the criteria, their combined number of units (counted only one time each) is 379.

I feel like you're so incredibly close to solving this mystery.

Thank you for everything you have done already.

Rick
 
Upvote 0
Gomesy said:
I got the formula in G1 to return a value of 4 (which I am assuming means the # of accounts that met the criteria in your formula). 4 is indeed the number of accounts that placed web orders at least once in 2014.

I don't understand what you mean when you write the word " List " for the value that is supposed to appear in G2. Can you explain please?

I copied and pasted your formula into G3 and got a value of 94. The desired output is 379. When you look at the 4 accounts that met the criteria, their combined number of units (counted only one time each) is 379.

I feel like you're so incredibly close to solving this mystery.

Thank you for everything you have done already.

Rick
Click to expand...

There is nothing mysterious about G2. It's just a header for the list of units the second formula outputs.

It seems you are just interested in (a) the count G1 shows and (b) the total of the relevant units. Right? Or is it just b?
 
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
634
Gomesy
G

Forum statistics

Threads
1,215,700
Messages
6,126,301
Members
449,308
Latest member
VerifiedBleachersAttendee

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