Counting Unique Values, but only if they are within a certain date range
As the title says, trying to count rows, but only unique values and within a certain date range. Formula I'm trying to use:
=COUNT(DISTINCT(COLLECT({SO Number}:{SO Number}, {Date}, AND(MONTH(@cell ) = 8)))
Example Data: Sales Order Number = {SO Number} and Actual Ship Date = {Date}
Any help please
Best Answer
-
Hi @Jack Parry
You were almost there. You don't need the AND as there is only one piece of logic for the date criteria (you would use AND if you were specifying a range such as after Jan but before July). But you do need to add an IFERROR to consider rows without a date as having a month of 0, otherwise they confuse the COLLECT.
Try changing
=COUNT(DISTINCT(COLLECT({SO Number}:{SO Number}, {Date}, AND(MONTH(@cell ) = 8)))
to
=COUNT(DISTINCT(COLLECT({SO Number}:{SO Number}, {Date}, IFERROR(MONTH(@cell ), 0) = 8)))
Let me know how you get on.
Answers
-
Hi @Jack Parry
You were almost there. You don't need the AND as there is only one piece of logic for the date criteria (you would use AND if you were specifying a range such as after Jan but before July). But you do need to add an IFERROR to consider rows without a date as having a month of 0, otherwise they confuse the COLLECT.
Try changing
=COUNT(DISTINCT(COLLECT({SO Number}:{SO Number}, {Date}, AND(MONTH(@cell ) = 8)))
to
=COUNT(DISTINCT(COLLECT({SO Number}:{SO Number}, {Date}, IFERROR(MONTH(@cell ), 0) = 8)))
Let me know how you get on.
-
@KPH I had to remove the ':{{SO Number}}', but the addition of the IFERROR worked perfectly. Thank you! :)
-
Opps, I didn't spot the :{SO Number} part
Well done!
-
@KPH Hi, I've taken this one step further to count the user who performed the action.
=COUNT(DISTINCT(COLLECT({Work Order}, {Who}, [User Name]@row, {Date}, IFERROR(MONTH(@cell ), 0) = 9)))
However when I compare that to the total data for the month using this formula:=COUNT(DISTINCT(COLLECT({Work Order}, {Date}, IFERROR(MONTH(@cell ), 0) = 8)))
Where have I gone wrong here…
-
Sorry Jack, I don't understand the problem. What has gone wrong?
-
@KPH The total should be either by 5 or 4.
It depends if the calculation looking at the Distinct WO #, User and Date is wrong or the calculation looking at just Distinct WO # and Date is wrong.
My guess is that its the addition of the user that is throwing the calculation off… -
Are you saying that when you include the user the total for one of the users is 1 and it should be 5?
In the two example formulas you shared above, one is for September and one is for August.
If you want to understand the date in the COLLECT, you can remove the COUNT and DISTINCT functions and use JOIN to show you what is being collected. That might help troubleshoot?
=JOIN(COLLECT({Work Order}, {Who}, [User Name]@row, {Date}, IFERROR(MONTH(@cell ), 0) = 9),",")
-
@KPH apologies not explaining myself very well.
What I'm saying is the Total = 4 (today now 7), therefore the sum of the users should also add up to 4 (today now 7).
It seems to be 1 out, am I missing a parenthesis on this calculation perhaps? Is the distinct formula also limiting the unique user count?=COUNT(DISTINCT(COLLECT({Work Order}, {Who}, [User Name]@row, {Date}, IFERROR(MONTH(@cell ), 0) = 9)))
See screenshots below using Join calculation as above: -
If you look at row 1, you have two work orders 02080457, one more pair and two singles. 4 distinct in total.
In row 2, you have 2 distinct work orders.
In row 5, you also have 2 work orders 02080457, then one single. 2 distinct in total.
When you add the totals 4 +2 + 2 you get 8.
However, if you use a count distinct for the total you will get 7 as the two work orders in row 1 that I highlighted (02080457) are the same as the two in row 5. There are only 7 distinct work orders.
Does that make sense?
Either "total" could be valid. It depends on your need.
-
@KPH that does make sense.
So if I just wanted a total of the number of distinct orders by all the users? (so 8)Would it be just a sum of the column or can we define our current formula better?
-
If you want a total of the distinct work orders per person (i.e. a sum of the rows above, 8 in the example) use
= SUM(
If you want a total of the distinct work orders in the period (i.e. each work order is only included once, 7 in the example) the
= COUNT(DISTINCT(
-
@KPH Is there a way of calculating the distinct values in a date range and of those distinct values calculating the user who performed the action?
=COUNT(DISTINCT(COLLECT({Work Order}, {Who}, [User Name]@row, {Date}, IFERROR(MONTH(@cell ), 0) = 7)))
Maybe using a countif function?
-
You can count the distinct work order in a date range but I'm not sure what you mean by "calculating the user who performed the action"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!