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!

Find more posts tagged with

Accepted answers

Paul Newcome

You don't need the individual AND functions in a COUNTIFS. Try removing them.

Paul Newcome

Ok. So there is a difference between your formula and your filter. Your formula is looking for January regardless of the year, but your filter is looking between specific dates. Is there an entry for January 2024?

Paul Newcome

For maintenance reasons, I find it is easier to specify month and year instead of in between two dates. Here is how you would do that:

=COUNTIFS({Date Range}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2025), …………………)

Susan Zimmerman

Thank you so much, this worked! To show the final version, the completed formula is:

=COUNTIFS({Sheet A Report Type}, OR(@cell = "Report Type 1", @cell = "Report Type 1"), {Sheet A Due Date}, AND(IFERROR(MONTH(@cell ), 0) = 1, IFERROR(YEAR(@cell ), 0) = 2025), {Sheet A Date Difference}, <2) + COUNTIFS({Sheet B Report Type}, OR(@cell ), {Sheet B Due Date}, AND(IFERROR(MONTH(@cell ), 0) = 1, IFERROR(YEAR(@cell ), 0) = 2025), {Sheet B Date Difference}, <2)

Paul Newcome

@Susan Zimmerman No. You will need that calculation in the source sheet.

All comments

Paul Newcome

You don't need the individual AND functions in a COUNTIFS. Try removing them.

Susan Zimmerman

As a follow-up,

=COUNTIFS({Completed Report Type}, OR(@cell = "Method Validation", @cell = "Method Qualification"), {Completed Due Date}, IFERROR(MONTH(@cell ), 0) = 1)

is returning one more than it should, and I can't figure out why.

Susan Zimmerman

https://community.smartsheet.com/discussion/comment/470847#Comment_470847

=COUNTIFS({Ithaca Reports Report Type}, OR(@cell = "Method Validation", @cell = "Method Qualification"), {Ithaca Due Date}, IFERROR(MONTH(@cell ), 0) = 1, {Ithaca Date Difference}, <2) + COUNTIFS({Completed Report Type}, OR(@cell = "Method Validation", @cell = "Method Qualification"), {Completed Due Date}, IFERROR(MONTH(@cell ), 0) = 1, {Completed Date Difference}, <2)

This worked to pull things in from multiple sheets, and I received a value. However, when I verify the parameters by filtering the two sheets to locate only Method Validation and Method Qualification Reports due in January, I find it was correct on the first sheet, but it is 1 more than it should be on the second sheet. Both parts of the formula are the same, so I don't understand why it would work in one and not the other.

Any ideas?

Paul Newcome

Double check that you have no collapsed parent rows or anything. Can you provide a screenshot of the filters you are using?

Susan Zimmerman

https://community.smartsheet.com/discussion/comment/470871#Comment_470871

In the second sheet, there should only be 1 value returned (row 8) based on these filters I used to verify, but the formula

=COUNTIFS({Completed Report Type}, OR(@ ), {Completed Due Date}, IFERROR(MONTH(@cell ), 0) = 1)

returns a value of two. This sheet doesn't use parent/child rows.

Paul Newcome

Susan Zimmerman

https://community.smartsheet.com/discussion/comment/470910#Comment_470910

Yes! Goodness. So glad you figured that out. In that case, how would I rewrite the formula to only read between 01/01/2025 and 01/31/2025? I will have to set all metrics by their month and this sheet will have some items from 2024 and 2025.

Paul Newcome

For maintenance reasons, I find it is easier to specify month and year instead of in between two dates. Here is how you would do that:

=COUNTIFS({Date Range}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2025), …………………)

Susan Zimmerman

Thank you so much, this worked! To show the final version, the completed formula is:

Susan Zimmerman

@paulnewcome For the final input, {Sheet B Date Difference}, <2, was with the goal to measure On-Time Delivery Rate — is there a way to do this without having to make that column subtracting two dates? For another metric, I have something else that needs to be completed within 20 days. Is it possible to have a COUNTIF function that effectively populates values if the {Finish Date} - {Start Date} ≤ 20 business days?

Paul Newcome

@Susan Zimmerman No. You will need that calculation in the source sheet.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.