Count how many per Month and Year for Summary Sheet?

Hey - I'm trying to create a summary sheet for photo data. I want to count how many Photo shoots are happening per month/year. The screenshot is below is our "Comp" sheet that has the data I want to pull for the summary sheet. So ideally, I can then create a graph for a dashboard.

The AI generated this formula: =COUNTIFS([OW PHOTO SHOOT TRACKING DATE]:[OW PHOTO SHOOT TRACKING DATE], MONTH(@cell) = 4, [OW PHOTO SHOOT TRACKING DATE]:[OW PHOTO SHOOT TRACKING DATE], YEAR(@cell) = 2024)

Error code is #UNPARSEABLE

*** This is a DATE column ***

Find more posts tagged with

Formulas

Sheet Summary

Accepted answers

Shawn_K2

Ok I found a solution. I'm going to create a helper column using this formula

=IF(ISDATE([OW PHOTO SHOOT TRACKING DATE]@row), IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 1, "January", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 2, "February", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 3, "March", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 4, "April", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 5, "May", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 6, "June", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 7, "July", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 8, "August", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 9, "September", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 10, "October", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 11, "November", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 12, "December", "")))))))))))), "")

I found the idea from @Colleen Patterson

https://community.smartsheet.com/discussion/101924/how-to-group-chart-dates-by-month-instead-of-individual-dates

Shawn_K2

Improved it to include the year😁

=IF(ISDATE([OW PHOTO SHOOT TRACKING DATE]@row), IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 1, "January ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 2, "February ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 3, "March ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 4, "April ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 5, "May ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 6, "June ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 7, "July ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 8, "August ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 9, "September ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 10, "October ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 11, "November ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 12, "December ", "")))))))))))) + YEAR([OW PHOTO SHOOT TRACKING DATE]@row), "")

All comments

Shawn_K2

Ok I found a solution. I'm going to create a helper column using this formula

I found the idea from @Colleen Patterson

https://community.smartsheet.com/discussion/101924/how-to-group-chart-dates-by-month-instead-of-individual-dates

Shawn_K2

Improved it to include the year😁

=IF(ISDATE([OW PHOTO SHOOT TRACKING DATE]@row), IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 1, "January ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 2, "February ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 3, "March ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 4, "April ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 5, "May ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 6, "June ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 7, "July ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 8, "August ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 9, "September ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 10, "October ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 11, "November ", IF(MONTH([OW PHOTO SHOOT TRACKING DATE]@row) = 12, "December ", "")))))))))))) + YEAR([OW PHOTO SHOOT TRACKING DATE]@row), "")

Georgie

Hey @Shawn_K2,

Thank you for posting your solution. I'm glad you got this resolved!

Georgie

Humashankar

Perfect !!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.