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?

 

[Gomesy]

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.

 

[Aladin Akyurek]

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.

You are welcome.

 

[Gomesy]

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

 

[Aladin Akyurek]

Would you create a concise sample illustrating the issue along with the desired result?

 

[Gomesy]

[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]Web Orders[/TD]
[TD]12560316[/TD]
[TD] 94[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD]12560316 [/TD]
[TD]94[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]Web Orders[/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]Web Orders[/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]

 

[Aladin Akyurek]

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.

 

[Gomesy]

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.

 

[Aladin Akyurek]

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.

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))))

 

[Gomesy]

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

 

[Aladin Akyurek]

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

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?