# Need Help with a Formula Again

Options
✭✭✭✭

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

«1

• ✭✭✭✭✭✭
Options

Can you please share a screenshot of the sheet?

Thank you

Itai Perez

Reporting and Project Manager

If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

You are awesome Paul! Thank you!

• ✭✭✭✭
Options

@Paul Newcome can you help me with another one? Sorry this one is stumping me.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

Did you still need help with the second ask, or were you able to find another thread / get it figured out?

• ✭✭✭✭
Options

@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

• ✭✭✭✭✭✭
Options

Are you able to provide a screenshot for context?

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

@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))

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

@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))

• ✭✭✭✭✭✭
Options

My apologies. I missed that you also need the comma between the last range/criteria set as well.

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

It looks like you've accidentally removed the ">" from before the first DATE criteria.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!