Capture Sheet Data Metrics by Current Year and Quarter

Tony Fronza

I am assisting our Marketing team with tracking social media statistics year over year broken out by quarter. I want to create a sheet summary field that captures metrics for the current year and a specific quarter to populate and auto-refresh on a dashboard to avoid manually modifying the metrics each quarter/year.

Is there a formula to find the current year and a specific quarter in the sheet data?

This is what I have so far but I'm receiving an Invalid Operation error:

=IF(AND(Year:Year = "2023", Quarter:Quarter = "1st"), [Total Account Posts]:[Total Account Posts], "0")

Paul Newcome

In that case, remove the quotes from around the year within the formula.

Nick Korna

Assuming your data has a date column you can use for this purpose and you're following the calendar year:

="Q" + ROUNDUP(MONTH([Date]@row) / 3, 0) + " " + YEAR([Date]@row)

If your quarters follow a different pattern then the formula would need some alterations, but would need to know what these are to do this.

Hope this helps, any problems/questions then just post! 🙂

Tony Fronza

Thanks @Nick Korna! I may have not been clear and just added the formula that I can't get to work - are you able to assist with that? Here's the formula again that I'm receiving an error for:

=IF(AND(Year:Year = "2023", Quarter:Quarter = "1st"), [Total Account Posts]:[Total Account Posts], "0")

I want to return the data in the field specified in bold text for the row. Otherwise, if it doesn't exist, enter 0.

Nick Korna

Are you wanting a count of the total account rows with 0 in quarter 1 2023?

=COUNTIFS(Year:Year,2023,Quarter:Quarter,"1st",[Total Account Posts]:[Total Account Posts],0)

would show how many instances of 0 Total Account posts were in quarter 1. If you wanted to count rows above a certain value of account posts you would alter the 0 to another value (e.g. ">0" instead of "0" to show positive values).

If you wanted a sum of them, then you would change it to SUMIFS instead:

=SUMIFS([Total Account Posts]:[Total Account Posts],Year:Year,2023, Quarter:Quarter,"1st")

Hope this is a bit more what you're after - let me know if I've misunderstood though!

Tony Fronza

No, sorry. I want the formula to return the data in the corresponding field within a specific row (i.e., If the year is 2023 and the quarter is 1st return a specific field's value). Make sense?

Paul Newcome

You would need an INDEX/COLLECT like so:

=IFERROR(INDEX(COLLECT([Total Account Posts]:[Total Account Posts], Year:Year, @cell = "2023", Quarter:Quarter, @cell = "1st"), 1), "")

Tony Fronza

Thanks, @Paul Newcome !

The formula returned nothing but there is data in the sheet:

Paul Newcome

How exactly are the Year and Quarter columns being populated?

Tony Fronza

Someone in Marketing populates the fields manually. I'm using the formula in a sheet summary field to update metrics on a Dashboard. My plan is to have the user update the year in each formula (each year) so the quarterly metric data refreshes (versus requiring to map the new quarter rows each year). Let me know if that makes sense!

Paul Newcome

In that case, remove the quotes from around the year within the formula.

Tony Fronza

You da man...as usual :) That worked perfectly.

Paul Newcome

Happy to help. 👍️