COUNTIFS Formula referencing a single cell value as a criteria

Options

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?

Tags:

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!