Formula Help

Jessica Howell

Hi everyone :)

Each year there is a new summary sheet for data reported, in that year.

The formula I am currently using has in between dates, and i would like it to recognize the quarter's column

If it says Q3, and the refrigerant type is R404a, then it counts 1.. and so on. Currently their is 11 for this quarter.

What I currently have in place: =COUNTIFS({Environmental Incident Tracking Range 2}, >=DATE(2020, 6, 14), {Environmental Incident Tracking Range 2}, <=DATE(2020, 9, 5), {Environmental Incident Tracking Range 5}, "R404a")

What I would like: =COUNTIFS({Environmental Incident Tracking Range 3}, "Q3", {Environmental Incident Tracking Range 5}, "R404a")

This example shows a 2 for the quarter. But it runs between options 1-4

Paul Newcome

It shouldn't. The only thing you should need to adjust is the criteria you are searching for in your formula. You have "1", "2", "3", or "4" as your data, so your criteria in your formula should match. "3" instead of "Q3".

David Tutwiler

I am sorry, but I'd ask for a point of clarification. Are you saying your first formula works if there are dates, but that you'd like to change the formula to look for the text Q3? If that's true, are you looking at a sheet like your first image suggests where the quarter is in the first row of the column?

Jessica Howell

Yes, looking to change it. Reason being, when I re-create the sheet next year, I would like to not have to change the date sequences in every cell.

Looking to pull everything from "Environmental Incident Tracking" to "SC- Refrigerant Leak Data"

Paul Newcome

Does the sheet with your source data contain a column that houses which quarter each row is in?

Jessica Howell

Yes. If I am understanding correctly. Each row will have their own quarter, that is generated depending in the given date

Paul Newcome

Then you should be able to use your second formula so long as that first range is the column on the source sheet where the quarter is housed.

Jessica Howell

The grey area (Year, quarter, period, and week) is fed from another sheet. But will that effect the formula?

Paul Newcome

It shouldn't. The only thing you should need to adjust is the criteria you are searching for in your formula. You have "1", "2", "3", or "4" as your data, so your criteria in your formula should match. "3" instead of "Q3".

Jessica Howell

That was such a simple mistake.. how did i miss that! Sometimes it just takes a second pair of eyes! thank you!

Paul Newcome

Happy to help. 👍️