Countif for Current Month in Column Name

Mike Norman

I am trying to pull a metric from my main sheet, where the columns are named by month (Apr Calls, May Calls, Jun Calls, Jul Calls, etc.). How can I pull the current month's result cleanly without having to update it by each month's number manually?

Paul Newcome

You would need to adjust the -1 portion in the formula. The -1 is indicating the last column in the month section -1. So for the GSK data you would need -4, CB is -3, so on and so forth.

Paul Newcome

Are you able to provide some screenshots for reference?

Mike Norman

So I'd like to be able to pull current month as a metric on the dashboard without having to manually change it each month. So for example, it would automatically refresh from May TR to Jun TR.

Is this possible?

Paul Newcome

You are going to need a helper column with a formula in it to pull the appropriate data then reference the cell(s) in this column in your metrics widget(s).

The formula is going to look something like this:

=INDEX([Jan GSK]@row:[Dec CIR]@row, 1, (MONTH(TODAY()) * 5) - 1)

Mike Norman

Thanks for the reply! I added a helper formula column, it came back as Unparseable.

Paul Newcome

Double check that I spelled the column names correctly. I assumed that you have those same 5 data points for each month going back to January and through to December.

Mike Norman

You are correct, I updated the column and it returned results! The only problem I'm seeing now, is they aren't pulling the results for May.

Here is a screenshot, I hid the other month's columns so you can see what it should be, and what I'm pulling.

Paul Newcome

You would need to adjust the -1 portion in the formula. The -1 is indicating the last column in the month section -1. So for the GSK data you would need -4, CB is -3, so on and so forth.

Mike Norman

Thank you for your help Paul!

Paul Newcome

Happy to help. 👍️