COUNTIFS Formula referencing a single cell value as a criteria
I have built a calendar for tracking communications sent by my team, and I am trying to use that sheet to create a dashboard from which we can easily see how many comms of each type we've sent out within a quarter, both current quarter and the previous one.
Our fiscal calendar follows a 13 week structure, so it's not always evenly split between months. For example, our current quarter started on 5/4/20 and ends on 8/2/20.
I have added a quarter column in this calendar, so I can tag each comm as occurring in Q1FY21, Q2FY21, and so forth. I've made this a restricted drop down.
At the top, I'd like to be able to have a set of formulas that count the number of each comm type per quarter. For example "Town Hall". I would like each of these formulas to reference the Quarter (in the Quarter column) so that as we change quarters, I can just update that drop down in the Quarter Column and the formula will update, so I won't have to change the formula itself.
This formula works:
=COUNTIFS([Communication Type]:[Communication Type], "Town Hall", (Quarter:Quarter, "Q1FY21"))
But this one does not
=COUNTIFS([Communication Type]:[Communication Type], "Town Hall", (Quarter:Quarter, "[Quarter]3"))
Can anyone recommend edits for this?
Answers
-
Hi Kate,
Since Quarter is a column name, you do not need it in quotes. Can you try this:
=COUNTIFS([Communication Type]:[Communication Type], "Town Hall", (Quarter:Quarter, Quarter3))
Best wishes,
Sravya
Thanks,
Sravya
-
Sadly, no. This still comes out as #UNPARSEABLE.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 409 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!