Counting Unique Values based on a date criteria
I am using this formula to count for unique values within a range of cell. I want to be able to use a criteria, a date for the range of cell, what should the formula look like?
=SUM((IF(FREQUENCY(MATCH(B2:B16,B2:B16,0),MATCH(B2:B16,B2:B16,0))>0,1)))
Comments
-
Hi,
Just to be clear, are you looking to:
- SUM unique values based on the criteria,
- COUNT the unique values, or;
- Return a list of the unique values.
Based on your example formula, you are looking to SUM, but your explanation suggest a COUNT. It's also difficult to see what criteria you're evaluating and what you're matching against as the formula has obviously come straight out of Excel.
Kind regards,
Chris McKay
-
Thank you for the quick reply Chris. Yes that came straight out of Excel (and trying to migrate to Smartsheet). I am actually looking for a Count of unique values of clients who placed an order on a specific date, where a client can place multiple orders a day.
-
You're more than welcome and I appreciate the clarification.
Smartsheet lacks a lot of the statistical formulas that we take for granted in Excel. Oh and the COUNTIF/COUNTIFS functions go not get along very well with dates so we'll need to use COLLECT.
To set yourself up with the data you need, I'd handle it like this:
- Assuming you have similar columns (i.e. Report Date, Customer Name, Order Date etc.) to what I've created in the first image below, you'll need to create a control column like my Daily Order Count.
- This is important: Insert a 1 in the first row of Daily Order Count. This column is essentially counting how many times a customer has ordered each day, with 1 being the 1st time, 2 the 2nd time etc. As it's impossible that the first row is a duplicate (because it's the first row), we include a 1.
- From row 2 onwards, insert this formula adapting the column names as appropriate:
=COUNT(COLLECT([Daily Order Count]:[Daily Order Count], [Daily Order Count]:[Daily Order Count], 1, [Order Date]:[Order Date], [Report Date]$1)) - You will need to drag this formula as far down as you have order data. New rows (e.g. below Omni Consumer Products) will inherit the formula automatically.
- You will need to set a date criteria to compare your data against. I've just created a date column and filled the first cell with a date. I'm in Australia, so my date format is DD/MM/YY. Alternatively, you could use an =TODAY() formula in the cell if you wanted to show only orders placed today.
- In the cell where you want to display your unique daily customer order count, insert the following formula:
=COUNT(COLLECT([Daily Order Count]:[Daily Order Count], [Daily Order Count]:[Daily Order Count], 1, [Order Date]:[Order Date], [Report Date]$1))
You might need to adapt it slightly to suit your column names, but this will at least put you on the right track.
Kind regards,
Chris McKay
-
You rock Chris! Thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!