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]

Just b

 

[Aladin Akyurek]

Just b

Control+shift+enter:

=SUM(IF(FREQUENCY(IF(Year=2014,IF(WebOrders="web orders",MATCH(Account,Account,0))),ROW(Account)-ROW(INDEX(Account,1,1))+1),Units))

 

[Gomesy]

Aladin, You are an absolute GENIUS. Magnificent. Bravo. Well done.

Thank you so very much. I truly appreciate this effort that you have made to solve the problem once and for all.

This is exactly why I love this site.

Take care my friend!

Rick

 

[Aladin Akyurek]

You are welcome.

 

[cricklyon]

Control+shift+enter:

=SUM(IF(FREQUENCY(IF(Year=2014,IF(WebOrders="web orders",MATCH(Account,Account,0))),ROW(Account)-ROW(INDEX(Account,1,1))+1),Units))

Thank you Aladin. Was searching for a solution to our needs and luckily found this thread. Thank you for your brilliant solution.

 

[Aladin Akyurek]

Thank you Aladin. Was searching for a solution to our needs and luckily found this thread. Thank you for your brilliant solution.

That's great. Thanks for the kind note.