COUNTIFS Formula referencing a single cell value as a criteria

Kate Hutchinson

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?

sravya.gupta127326

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

Kate Hutchinson

Sadly, no. This still comes out as #UNPARSEABLE.