nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Welcome!

Counting Unique Values based on a date criteria

ODT

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

Find more posts tagged with

Formulas

Comments

Chris McKay

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

ODT

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.

Chris McKay

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

ODT

You rock Chris! Thank you so much!

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIFS formula bringing back total of all items, not items in specified date range.
Hello, I'm using a SUMIFS formula to try and get the sum of materials, done during a specific process, during the specified date range of May 04 2026 - May 08 2026. But it continues to total all entries in that column, instead of giving the total based on the provided data. I've also tried using both OR and AND functions…
Creating Project Planning Sheets from a Template, Linking to Master Sheet
I created a master sheet that will list all projects for a department. We will also have a planning sheet for each project in the master sheet, created by copying one of four templates (based on the project type). Each of the four templates will have columns set up and a hierarchy of rows based on phases, workstreams,…
Using a checkbox in formula
Hello All, My existing formula (working correctly) inserts text of REORDER NEEDED or OK option , formula below: =IF([Stock Quantity]32 < [Reorder Level]32, "REORDER NEEDED", "OK") I would like to add a third text insert of "ORDERED" when criteria of a checkbox has been checked in the ON ORDER column -or a date ordered…