# Counting and Dividing Certain Cells

edited 03/03/23

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.

• ✭✭✭✭✭✭

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.

• edited 03/06/23

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.

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!