Nested COUNTIFs within Date Ranges
Hi Smartsheet Experts, I need some help creating a formula.
The Source sheet contains the data and I have formulas on another sheet; therefore, the formula will contain cross-sheet references.
Source Sheet looks something like this:
I want to count the number of Items that have an ORIGINAL or NEW date in January 2021 but are not Cancelled. In other words, COUNTIF ‘NEW Date’ has a date within a date range, excluding Cancelled Items. IF NEW Date is blank, COUNTIF ‘ORIGINAL Date’ has a date within same date range, excluding Cancelled Items.
This seems simple, but I cannot get the formula to work:
a. Row 8, ORIGINAL Date is within the date range but Status is blank, therefore is excluded from the total count. The Source Sheet ‘Status’ might not be available, and we need to include the Item within the total date range Count, even if the Status is blank.
b. As per the highlighted cells, the total should be 5.
Thank you so much for your help!
Emily
Best Answer
-
I'm not sure why it wouldn't pick up on the blank statuses since blank is not "Cancelled".
We can shorten it down quite a bit though by using an OR function (similar to how we used the AND function for the dates) to combine not cancelled and blank into the same argument instead of having to repeat the entire formula all over again.
=COUNTIFS({Status}, OR(@cell <> "Cancelled", @cell = ""), {NEW Date}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2021)) + COUNTIFS({Status}, OR(@cell <> "Cancelled", @cell = ""), {NEW Date}, @cell = "", {ORIGINAL Date}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2021))
Answers
-
Try something like this...
=COUNTIFS({Status}, @cell <> "Cancelled", {NEW Date}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2021)) + COUNTIFS({Status}, @cell <> "Cancelled", {NEW Date}, @cell = "", {ORIGINAL Date}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2021))
-
Hi @Paul Newcome, I knew if anyone could figure it out, it would be you!
Thank you for the formula. I changed "7" to "1" to pull January dates. It worked but the total was still missing the STATUS = Blank. So, I copied your formula and included @cell is blank and it worked!!! Here is the updated formula:
=COUNTIFS({STATUS}, @cell <> "Cancelled", {NEW DATE}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021)) + COUNTIFS({STATUS}, @cell <> "Cancelled", {NEW DATE}, @cell = "", {ORIGINAL DATE}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021)) + COUNTIFS({STATUS}, @cell = "", {NEW DATE}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021)) + COUNTIFS({STATUS}, @cell = "", {NEW DATE}, @cell = "", {ORIGINAL DATE}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021))
This works, I changed the source data and the correct updated values appeared. Since it works, I will run with it, please let me know if there is a more efficient formula I should be using.
Thanks, Emily
-
I'm not sure why it wouldn't pick up on the blank statuses since blank is not "Cancelled".
We can shorten it down quite a bit though by using an OR function (similar to how we used the AND function for the dates) to combine not cancelled and blank into the same argument instead of having to repeat the entire formula all over again.
=COUNTIFS({Status}, OR(@cell <> "Cancelled", @cell = ""), {NEW Date}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2021)) + COUNTIFS({Status}, OR(@cell <> "Cancelled", @cell = ""), {NEW Date}, @cell = "", {ORIGINAL Date}, AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2021))
-
Hi @Paul Newcome, the formula works great. Thank you so much.
Emily
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!