Count how many per Month and Year for Summary Sheet?

Shawn_K2

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 ***

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

How to group chart dates by month instead of individual dates

WasteKnot

Mar 1, 2023

https://us.v-cdn.net/6031209/uploads/1MYA5BXK6DFA/image.png

This is what I have started on a dashboard. The horizontal axis are individual dates for projected project completion. I want the chart to summarize by month. I've read through a bunch of posts about creating a helper column that pull the month number, but I am not following how that will translate to the chart when we are projecting into the next year. Everything I've read seems to be overly complex compared to what I am used to. I can easily add a helper column to translate each date to say the last day of that month, but again - am I missing a simple formula for that?

Thanks

---

View Post

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), "")

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

How to group chart dates by month instead of individual dates

WasteKnot

Mar 1, 2023

https://us.v-cdn.net/6031209/uploads/1MYA5BXK6DFA/image.png

This is what I have started on a dashboard. The horizontal axis are individual dates for projected project completion. I want the chart to summarize by month. I've read through a bunch of posts about creating a helper column that pull the month number, but I am not following how that will translate to the chart when we are projecting into the next year. Everything I've read seems to be overly complex compared to what I am used to. I can easily add a helper column to translate each date to say the last day of that month, but again - am I missing a simple formula for that?

Thanks

---

View Post

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 !!