Counting and Dividing Certain Cells

lugo

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.

Paul Newcome

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.

lugo

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

Paul Newcome

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

lugo

Like this?

that still doesn't seem to work.

Paul Newcome

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

lugo

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?

Paul Newcome

Try wrapping each YEAR and MONTH function in its own IFERROR

IFERROR(YEAR(@cell), 0) = 2023

IFERROR(MONTH(@cell), 0) = 1

lugo

That worked! Thank you so much.

Paul Newcome

Happy to help. 👍️