Counting and Dividing Certain Cells
I want to divide the number of requests that were completed on time by the total amount of requests submitted that month. I'm referencing another sheet in my formula.
The following is giving me "UNPARSEABLE"
=IF({On Time?} = "Yes", {Submitted Date}, YEAR(@cell) = 2023, {Submitted Date}, MONTH(@cell) = 1) / ({Submitted Date}, YEAR(@cell) = 2023, {Submitted Date}, MONTH(@cell) = 1)
Do I need a COUNTIF function somewhere?
I'm pretty new to Smartsheet so I appreciate the help.
Answers

You would need
=COUNTIFS(.............................) / COUNTIFS(..............................)
The second COUNTIFS would be the {Submitted Date} portion. The second COUNTIFS would be the same but also include the {On Time?} portion.

@Paul Newcome Sorry, the first or second would have the on time portion? I tried the format you suggested and it gave me "INVALID"
Here's what I have now:

Your first COUNTIFS needs a comma between the first range and first criteria.

Like this?
that still doesn't seem to work.

Yes. What is the exact error you are getting? There are a handful that start with "Invalid", and each could have different troubleshooting steps.

It's giving me "Invalid Data Type". The column type is text/number and the Yes/No column I'm pulling from is also text/number. The date column is a date type (not text/number) with a DATEONLY formula, could that be why?

Try wrapping each YEAR and MONTH function in its own IFERROR
IFERROR(YEAR(@cell), 0) = 2023
IFERROR(MONTH(@cell), 0) = 1

That worked! Thank you so much.

Help Article Resources
Categories
Check out the Formula Handbook template!