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
-
You don't need the individual AND functions in a COUNTIFS. Try removing them.
-
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?
-
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), …………………)
-
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)
-
@Susan Zimmerman No. You will need that calculation in the source sheet.
Answers
-
You don't need the individual AND functions in a COUNTIFS. Try removing them.
-
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.
-
=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?
-
Double check that you have no collapsed parent rows or anything. Can you provide a screenshot of the filters you are using?
-
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.
-
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?
-
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.
-
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), …………………)
-
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)
-
@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?
-
@Susan Zimmerman No. You will need that calculation in the source sheet.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 146 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!