Formula Multiple Parameters with Multi-Sheet Reference

I need to create a set of formulae using references to another sheet to:

  • 1). count the number of different types of reports due within the month of January, and
  • 2). the number of those reports delivered on time.

For the first formula I tried: COUNTIF({{Report Type}}, OR(@cell = "Report Type 1", @cell = "Report Type 2"), AND({Completed Due Date}, IFERROR(MONTH(@cell), 0) = 1).

I have the on-time delivery being measured by a column formula measuring the NETWORKDAYS between the date due and the date sent to the client, so a cell = 1 is on time, <1 is ahead of schedule, >1 is late. I would need to reference this sheet with anything <2 being "On-Time."

When I try each formula individually, they work:

  • =COUNTIFS({Report Type}, OR(@cell = "Sample Analysis", @cell = "Sample Analysis Disc"))
  • =COUNTIF({Due Date}, IFERROR(MONTH(@cell), 0) = 1)
  • =COUNTIF({Date Difference}, <2)

But putting them together with COUNTIFS like this:

=COUNTIFS({Report Type}, OR(@cell = "Sample Analysis", @cell = "Sample Analysis Disc"), AND({Due Date}, IFERROR(MONTH(@cell ), 0) = 1), AND({Date Difference}, <2))

yields an #INVALIDDATATYPE error or #UNPARSEABLE error.

How would I build this formula?

Of note, I will need to expand this formula to pull from two difference sheets.

Thanks for the help!

Best Answers

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!