Nested COUNTIFs within Date Ranges

EmilyH

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

Paul Newcome

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))

Paul Newcome

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))

EmilyH

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

Paul Newcome

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))

EmilyH

Hi @Paul Newcome, the formula works great. Thank you so much.

Emily

Paul Newcome

Happy to help. 👍️