# Counting Dates within a Date column

Hi all,

I'm trying to set up a formula that counts specific dates within a date column, using the following approach:

=COUNTIF({Reference 1}, "31-12-2020")

It keeps returning 0 when there are least 4 dates within the target column that match the specified criteria.

Can anyone advise how I can fix this?

Tags:

• ✭✭✭✭✭✭

Is it allowed to you create new sheet, if so you can create summary sheet to make all the calculation you need and use a reference for the data you need in your calculation the structural source data sheet.

PMP Certified

[email protected]

www.mobilproject.it

• ✭✭✭✭✭✭

This works for me...

Are you absolutely sure that what you put on here is exactly what you have on your sheet, as I can't see anything wrong with your formula! :D

The reference seems to have changed from {REFERENCE1} to {Portfolio Plan Range 3} this is the same date column? or if it is different, clarify that it is a date column that you are pointing to and that you have set up the range in the current sheet and not just typed it in from another formula on a different sheet? (Each sheet need the cross sheet references re-defining - this would put up an unparseable error if the range had been copied from another sheet and not redefined)

=COUNTIFS({Portfolio Plan Range 3}, ISDATE(@cell), {Portfolio Plan Range 3}, AND(DAY(@cell) = 31, MONTH(@cell) = 12, YEAR(@cell) = 2020))

Try copying and pasting this one in to your sheet. (ensuring {Portfolio Plan Range 3} was defined and named on the current sheet.

Pop a screen shot up on it in place if you are still getting unparseable.

Good luck

Debbie

• ✭✭✭✭✭✭
edited 12/08/20

Hope you are fine, your problem is when you define the criteria in count formula as a text so the result for sure will be 0, to solve this problem create help column for the criteria and input in that column the date you need to count then the formula will work correctly, i create for you a sample please check the following screen shot:

1- Reference column & Date Column are Date type.

2- Count column is Text/Number type

3- the formula in count column =COUNTIFS([Date Column]:[Date Column], Reference@row)

PMP Certified

[email protected]

www.mobilproject.it

• I'm very well thank you, I hope you are too. Thank you for replying to me and for your clarification. Unfortunately, due to the structural requirements of the source data, I cannot input helper columns.

Is there any other way I can work around this? I tried running a report isolating the dates in question, so that I could run a simple =COUNT formula, but it seems Smartsheet doesn't consider Reports to be referenceable.

Many thanks,

Gerhard

• ✭✭✭✭✭✭

Is it allowed to you create new sheet, if so you can create summary sheet to make all the calculation you need and use a reference for the data you need in your calculation the structural source data sheet.

PMP Certified

[email protected]

www.mobilproject.it

• I had thought of this option, but I was hoping to avoid duplication of data and use of multiple data sources.

Regardless, thank you again for your time and help, it is much appreciated!

Kind regards,

Gerhard Costa Pinto

• ✭✭✭✭✭✭

You are welcome

PMP Certified

[email protected]

www.mobilproject.it

• ✭✭✭✭✭✭

You could add a helper date field in the Sheet Summary section and reference that in the formula instead.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

Hi

I haven't visited the community for a while and I appreciate you have an accepted answer on this - I just wanted to add that you can achieve this without helper columns if you wanted to. I have created a little example for you. It works for me.

Hope this helps.

Kind regards

Debbie

• Thank you so much for getting back to me and for your suggestion. I've just tried your suggestion and got an #UNPARSEABLE error.

Have I missed something? Please note, that I am referencing another sheet, but REFERENCE 1 in the formula below is the date column in question.

=COUNTIF({REFERENCE 1},AND((DAY(@cell)=31,Month(@cell)=12,Year(@cell)=2020))

Kind regards,

Gerhard

• ✭✭✭✭✭✭
edited 12/08/20

I believe you may have an extra ( in your formula...Try this

=COUNTIF({REFERENCE 1},AND(DAY(@cell)=31,Month(@cell)=12,Year(@cell)=2020))

• ✭✭✭✭✭✭

Just realised, if your cross sheet reference is referring to a column where there might be unfilled cells (Blank cells) then you might get an Invalid data type error return.

This formula fixes that:

=COUNTIFS({REFERENCE 1}, ISDATE(@cell), {REFERENCE 1}, AND(DAY(@cell) = 15, MONTH(@cell) = 12, YEAR(@cell) = 2020))

Hope this helps! 🤩

Kind regards

Debbie

• Hi Debbie,

Thank you again for getting back to me! You were right in that the column being referenced has blank cells in it, however the proposed formula to solve this comes back as #UNPARSEABLE.

I'm fairly certain I got everything exactly as your formula, so I'm not sure why this isn't working.

=COUNTIFS({Portfolio Plan Range 3},ISDATE(@cell),{Portfolio Plan Range 3},AND(DAY(@cell)=31,MONTH(@cell)=12,Year(@cell)2020))

Any ideas?

Kind regards,

Gerhard

• ✭✭✭✭✭✭

This works for me...

Are you absolutely sure that what you put on here is exactly what you have on your sheet, as I can't see anything wrong with your formula! :D

The reference seems to have changed from {REFERENCE1} to {Portfolio Plan Range 3} this is the same date column? or if it is different, clarify that it is a date column that you are pointing to and that you have set up the range in the current sheet and not just typed it in from another formula on a different sheet? (Each sheet need the cross sheet references re-defining - this would put up an unparseable error if the range had been copied from another sheet and not redefined)

=COUNTIFS({Portfolio Plan Range 3}, ISDATE(@cell), {Portfolio Plan Range 3}, AND(DAY(@cell) = 31, MONTH(@cell) = 12, YEAR(@cell) = 2020))

Try copying and pasting this one in to your sheet. (ensuring {Portfolio Plan Range 3} was defined and named on the current sheet.

Pop a screen shot up on it in place if you are still getting unparseable.

Good luck

Debbie

• Debbie, not exactly sure what changed but this did it! Thank you so much🤩!!

• ✭✭✭✭✭✭

Yay!

The only thing I could see was that Year was mixed case and not caps - but I can't imagine that was the error!

Glad it is working for you.

Kind regards

Debbie

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!