Sheet Summary CountIFS + Date Range

MoniqueM

Hi All,

Need some assistance. Found a great post & this on the subject but was not able to get my formulas to work. Hoping the group can help. I have 1 working the other is not.

Background: Form was converted over from Google Form/Sheets & several formatting changes had to be made in August. (Probably issue #1)

Co-workers fill out the form and entries are collected. I added an "AUTO-NUMBER/SYSTEM" & it is set to [ Created Date ]. <--- I need to tally these entries by months so I can create a dashboard with a line chart showing the trend of entries. See below:

In the Sheet summary I have this: =COUNTIFS(Created:Created, IFERROR((@cell), 0) >= DATE(2021, 11, 1), Created:Created, IFERROR((@cell), 0) <= DATE(2021, 11, 30))

This formula works for entries for Sept, Oct, Nov, Dec ONLY

--------------------------------------------------------------------------------------------------

Months April, May, June, July were pre-Smartsheet...

I do still have the Google Date created column set to "DATE" . I changed the formula and get a " #UNPARSEABLE " Error. Using formula: =COUNTIFS(GoogleForm Created Date:GoogleForm Created Date, IFERROR((@cell), 0) >= DATE(2021, 5, 1), GoogleForm Created Date:GoogleForm Created Date, IFERROR((@cell), 0) <= DATE(2021, 5, 31))

Any ideas? 😥

Jeff Reisman

I have a few thoughts here. If I understand correctly you're having issues with dates for August? Try creating a helper column for your Created dates, call the Column CreatedDate. Use the DATEONLY function against the values in the system-generated Created column:

=DATEONLY(Created@row)

Use the CreatedDate column as the date range in your formulas.

For your pre-Smartsheet dates, you need brackets around your column names since they are multiple word column names:

=COUNTIFS([GoogleForm Created Date]:[GoogleForm Created Date], IFERROR((@cell), 0) >= DATE(2021, 5, 1), [GoogleForm Created Date]:[GoogleForm Created Date], IFERROR((@cell), 0) <= DATE(2021, 5, 31))

See if that works better.

Jeff Reisman

Is there a way to delete the cell contains of just those 43 dates without remove the full format of that column? I need it to keep as "Auto-Number"... Is it safe to switch to Text/Number, delete, then switch back?

While this works with system-generated fields defined as Auto-Number (like Item0001, Item0002, etc.,) for the Created (Date) and Modified (Date) fields it does not. You can convert it to Text/Number, delete values, and change it back to system-generated, but the original Created dates will come right back after saving the sheet.

Why not just manually fill in the GoogleDate column for the three created dates you want to count in August, and use the Google Date formula for the August summary?

Jeff Reisman

I have a few thoughts here. If I understand correctly you're having issues with dates for August? Try creating a helper column for your Created dates, call the Column CreatedDate. Use the DATEONLY function against the values in the system-generated Created column:

=DATEONLY(Created@row)

Use the CreatedDate column as the date range in your formulas.

For your pre-Smartsheet dates, you need brackets around your column names since they are multiple word column names:

=COUNTIFS([GoogleForm Created Date]:[GoogleForm Created Date], IFERROR((@cell), 0) >= DATE(2021, 5, 1), [GoogleForm Created Date]:[GoogleForm Created Date], IFERROR((@cell), 0) <= DATE(2021, 5, 31))

See if that works better.

MoniqueM

@Jeff Reisman

"... your column names since they are multiple word column names:..." <--- That solved the one issue. Change it be 1 word "GoogleDate" formula is now: =COUNTIFS(GoogleDate:GoogleDate, IFERROR((@cell), 0) >= DATE(2021, 5, 1), GoogleDate:GoogleDate, IFERROR((@cell), 0) <= DATE(2021, 5, 31))  

--- New issue occurred -- August is the problem child... August is when I converted over and added that Auto-Number Column, I have 43 entries at the beginning that all have 8/16/2021. (I have 46 total for that date, only 3 need to be counted.)

My thought is to just do this, I feel like this isn't a good solution: =COUNTIFS(Created:Created, IFERROR((@cell), 0) >= DATE(2021, 8, 1), Created:Created, IFERROR((@cell), 0) <= DATE(2021, 8, 31)) - 43

Is there a way to delete the cell contains of just those 43 dates without remove the full format of that column? I need it to keep as "Auto-Number"... Is it safe to switch to Text/Number, delete, then switch back?

Jeff Reisman

Is there a way to delete the cell contains of just those 43 dates without remove the full format of that column? I need it to keep as "Auto-Number"... Is it safe to switch to Text/Number, delete, then switch back?

While this works with system-generated fields defined as Auto-Number (like Item0001, Item0002, etc.,) for the Created (Date) and Modified (Date) fields it does not. You can convert it to Text/Number, delete values, and change it back to system-generated, but the original Created dates will come right back after saving the sheet.

Why not just manually fill in the GoogleDate column for the three created dates you want to count in August, and use the Google Date formula for the August summary?

MoniqueM

Yep! I ended up just added August's entries to the GoogleDate column. 🙃 Thank you for your help!