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

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
Aladin Akyurek said:
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))
Click to expand...

Thank you Aladin. Was searching for a solution to our needs and luckily found this thread. Thank you for your brilliant solution.
 
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
647
Gomesy
G

Forum statistics

Threads
1,216,477
Messages
6,130,879
Members
449,603
Latest member
dizze90

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