2 counting dates in a column formulas
I have two simple counting formulas that aren't working for me.
I have searched other questions/answers and tried their solutions, but it still doesn't work for me and I don't understand why.
Here's a snippet of my data:
The first thing I'm trying to do is count how many dates (not counting days) are listed in "Year Certified" IF "Year Ended" is blank. Here is the formula I am trying:
=COUNTIFS([Year Certified]:[Year Certified], <=TODAY, [Year Ended]:[Year Ended], ISBLANK)
This doesn't have to be specified as a date less than today, as all dates entered will be less than today, by value of the data. So the <=TODAY is not necessary in the formula.
The second thing I'm trying to do is count how many dates (not days) are listed in "Monitored till" that are greater than todays date. (The cells in this column are populated by a formula so all have a date, though those dates prior today are invisible). The formula I have is: =COUNTIFS([Monitored till]:[Monitored till], @cell >= TODAY())
Appreciate any help. Thanks.
Answers
-
Hey @A. Jasa
If you are wanting to count dates and not days, you need the DISTINCT function. Because you are need to filter the data for the blank [Year Ended] cells, we must also use the COLLECT function so we can add criteria to the COUNT/DISTINCT combination.
To make sure that blank [Year Ended] unused rows at the bottom of the sheet are not also counted, I added your [Year Certified] criteria.
=COUNT(DISTINCT(COLLECT([Year Certified]:[Year Certified], [Year Certified]:[Year Certified],<TODAY(), [Year Ended]:[Year Ended], ="")))
=COUNT(DISTINCT(COLLECT([Monitored till]:[Monitored till], [Monitored till]:[Monitored till], ISDATE(@cell), [Monitored till]:[Monitored till],>TODAY())))
I notice you have an error in the [Monitored Till] column. Seeing this, I tried to filter this out using the ISDATE() criteria. If the above count formula, once inserted in your sheet, produces an error, I will need to tweak the formula and add an IFERROR.
Do these formulas work for you?
Kelly
If interested, this reference offers information on the DISTINCT function
-
Thank you, Kelly. You are awesome.
I will check out the DISTINCT function reference, as this is something new for me.
The second formula worked like a charm.
The first formula worked, but I did not get the result I should have. It calculated '4', where as the real answer should be something like '15'.
In case it is helpful, a bit more information about what I'm trying to do is this. I'm counting how many consultants are certified (marked by a date in 'Year Certified') but subtracting how many have ended their consultancy (marked in 'Year Ended').
Any further thoughts on this one?
Amber
-
Hey @A. Jasa
Is each row a different consultant or are there duplicate consultant names? It sounds like you may want to use:
=Count(Distint(Counsultant:Counsultant))-COUNT(DISTINCT(COLLECT([Year Certified]:[Year Certified], [Year Certified]:[Year Certified],<TODAY(), [Year Ended]:[Year Ended], ="")))
*Be sure to edit the formula to use your actual column name for the one I called 'Consultant'.
Does the formula above get you closer to what you expect?
Kelly
-
Hum. That is not parseable.
Is the dash (-) bweteen Consultant))-COUNT subtract? If so, could I not use:
=COUNT(DISTINCT(COLLECT([Year Certified]:[Year Certified], [Year Certified]:[Year Certified],<TODAY()))-COUNT(DISTINCT(COLLECT([Year Ended]:[Year Ended],<TODAY())))
This works but it also doesn't give the correct answer.
But I wonder if either of these is only counting different numbers. If some dates were the same, could they be prevented from being counted in either this or your first suggestion?
Amber
-
Yes, if that gives you the right answer, certainly use it. I can't see the relationship between your consultants and dates so whatever works, use it.
As an fyi, an Unparseable typically means a column name, comma and/or parenthesis/bracket is wrong or out of place.
-
It works, but gives the wrong answer. So I haven't come up with a solution yet.
But thank you for all you help so far. Much appreciated.
-
I figured out the problem.
The COUNT(DISTINCT(COLLECT is only counting different numbers. "1, 2, 2, 3 will produce a value of 3 because only 1, 2, 3 are distinct."
I'm only using a subset of data to figure out all the formulas and pathways and I entered all the same 'Certified Date' because I didn't have that data at the time I started this. I have gone back and changed those dates and now your (Kelly Moore) original formula produces the correct calculation. But I have had to change all the 'Certified Date' to be distinct in order to get it.
In the future it is conceivable that more than one consultant could be certified on the same date.
So I removed the DINTINCT but kept the COLLECT.
=COUNT (COLLECT([Year Certified]:[Year Certified], [Year Certified]:[Year Certified],<TODAY(), [Year Ended]:[Year Ended], =""))
It worked!
Thank you so much.
-
Great! glad you figured it out. Yes, Distinct counts each different date only one time - I misunderstood your statement of only wanting dates not days. If you can have more than one Contractor on the same date you may want to explore the suggestion of Collecting the Contractor names/IDs instead of the dates, keeping all other criteria the same.
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!