Need Help with a Formula Again
Ok Team,
I am trying to say the following:
If APP SUB TO CVO/MSO]@row is blank then Grey
If APP SUB TO CVO/MSO]@row - [FACILITY HANDOFF (FHO) DATE]@row is less than 7 days then Green
If APP SUB TO CVO/MSO]@row - [FACILITY HANDOFF (FHO) DATE]@row is between 7 and 10 days then Yellow
If [APP SUB TO CVO/MSO]@row - [FACILITY HANDOFF (FHO) DATE]@row is more then 10 days then Red
With the ones that are in between I get an return of an incorrect argument
Sorry that I stink at formulas but below is what I put in
=IF([APP SUB TO CVO/MSO]@row = "", "Gray", IF([APP SUB TO CVO/MSO]@row - [FACILITY HANDOFF (FHO) DATE]@row < (7), "Green", IF([APP SUB TO CVO/MSO]@row - [FACILITY HANDOFF (FHO) DATE]@row > (10), "Red", IF(AND([APP SUB TO CVO/MSO]@row - [FACILITY HANDOFF (FHO) DATE]@row < (10), -[FACILITY HANDOFF (FHO) DATE]@row < (7), "Yellow")))))
Answers
-
Itai Perez
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
-
I would think you wouldn't need the "in between" arguments since you are already establishing the two ends. If it isn't less than 7 and isn't greater than 10, then it must be between which means you can just use "Yellow" as the final output without an IF.
=IF([APP SUB TO CVO/MSO]@row = "", "Gray", IF([APP SUB TO CVO/MSO]@row - [FACILITY HANDOFF (FHO) DATE]@row < (7), "Green", IF([APP SUB TO CVO/MSO]@row - [FACILITY HANDOFF (FHO) DATE]@row > (10), "Red", "Yellow")))
If you really want to be able to specify the "in between" arguments for the "Yellow", then your existing formula only has a single misplaced parenthesis from forgetting to close out the AND statement. Move one from the very end to immediately after the last (7). You also forgot the first part of that calculation.
You have
AND(this - that < 10, - that < 7)
And as I typed that out, I realized that last argument should be greater than 7 instead of less than 7.
-
You are awesome Paul! Thank you!
-
@Paul Newcome can you help me with another one? Sorry this one is stumping me.
-
Happy to help. 👍️
Did you still need help with the second ask, or were you able to find another thread / get it figured out?
-
@Paul Newcome I do still need help.
I am trying to count certain Providers within one Quarter per year that require reappointment date by Person. And I keep messing it up
-
-
This is the primary Sheet and I want the data to flow to the Summary sheet below
But I think error is that the Naming Convention is not the same. Helping someone build a dashboard and she has a lot of metrics.
I wish to count how many reappointments each CS has within each quarter of the year. but my formula comes back UNPARSEABLE
-
The image is a bit fuzzy, but it looks like your syntax is off a bit. The first range/criteria set should have the comma in between the same as the rest of the range/criteria sets, and you can get rid of the AND function completely.
-
@Paul Newcome I don't think I understand Is this correct?
=COUNTIFS({Reappointment Master Contact}=[Primary Column]@row, {Reappointment Master Date}, =DATE(2024, 1, 1), {Reappointment Master Date} <= DATE(2024, 3, 31))
-
You have
=COUNTIFS(rangecriteria, range, criteria, range, criteria)
You need a comma between the first range and first criteria the same way you have it for the second and third range/criteria sets.
-
@Paul Newcome It still says "Invalid Operation"
=COUNTIFS({Reappointment Master Contact}, [Primary Column]@row, {Reappointment Master Date}, =DATE(2024, 1, 1), {Reappointment Master Date} <= DATE(2024, 3, 31))
-
My apologies. I missed that you also need the comma between the last range/criteria set as well.
-
@Paul Newcome I am sorry I am annoying but it finally returned a value but all zeros…..I don't know what I am doing wrong.
-
It looks like you've accidentally removed the ">" from before the first DATE criteria.
Help Article Resources
Categories
Check out the Formula Handbook template!
