Formula Help
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 14
Best Answer

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".
Answers

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?

Yes, looking to change it. Reason being, when I recreate 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"

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

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

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.

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

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".

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

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!