2 counting dates in a column formulas

A. Jasa

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.

Kelly Moore

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

https://help.smartsheet.com/function/distinct

A. Jasa

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

Kelly Moore

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

A. Jasa

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

Kelly Moore

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.

A. Jasa

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.

A. Jasa

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.

Kelly Moore

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