Countifs across multple sheets with a Checkbox

I'm new to smartsheets, just getting my feet wet at thsi point. I could use some assistance with my Countifs expression which is across multiple sheets. My first expression:

=COUNTIFS({JAILS: Individual Bookings Arrest}, $[Column 11]@row, {JAILS: Individual Bookings ArrestDate}, >=DATE(2022, 8, 26), {JAILS: Individual Bookings Arrest}, $[Column 11]@row, {JAILS: Individual Bookings ArrestDate}, <=DATE(2022, 9, 1)) + COUNTIFS({Archived Jails: Individual Bookings Range 1}, $[Column 11]@row, {Archived Jails: Individual Bookings Range 2}, >=DATE(2022, 8, 26), {Archived Jails: Individual Bookings Range 1}, $[Column 11]@row, {Archived Jails: Individual Bookings Range 2}, <=DATE(2022, 9, 1))

works fine. At this point I need to add another condition which is a Checkbox column. I've written out the following, but returns "0" which is incorrect.

=COUNTIFS({JAILS: Individual Bookings Arrest}, $[Column 11]@row, {JAILS: Individual Bookings ArrestDate}, >=DATE(2022, 9, 16), {JAILS: Individual Bookings Arrest}, $[Column 11]@row, {JAILS: Individual Bookings ArrestDate}, <=DATE(2022, 9, 22), {JAILS: Individual Bookings HistoryCompleted}, 1) + COUNTIFS({Archived Jails: Individual Bookings Range 1}, $[Column 11]@row, {Archived Jails: Individual Bookings Range 2}, >=DATE(2022, 9, 16), {Archived Jails: Individual Bookings Range 1}, $[Column 11]@row, {Archived Jails: Individual Bookings Range 2}, <=DATE(2022, 9, 22), {Archived Jails: Individual Bookings ArchiveHisCom}, 1)

I've tried writing this in multiple ways, but always get the same result, "0".

Any assistance and guidance the community could provide would be greatly appreciated.

Thank you,

Tags:

Answers

  • Hi @Haus

    The structure of this looks correct! The only thing I can see right away is that you have a range and criteria duplicated. That shouldn't necessarily be an issue, but it may help clean up the formula a bit!

    I've bolded that area here:

    =COUNTIFS({JAILS: Individual Bookings Arrest}, $[Column 11]@row, {JAILS: Individual Bookings ArrestDate}, >=DATE(2022, 9, 16), {JAILS: Individual Bookings Arrest}, $[Column 11]@row, {JAILS: Individual Bookings ArrestDate}, <=DATE(2022, 9, 22), {JAILS: Individual Bookings HistoryCompleted}, 1) + COUNTIFS({Archived Jails: Individual Bookings Range 1}, $[Column 11]@row, {Archived Jails: Individual Bookings Range 2}, >=DATE(2022, 9, 16), {Archived Jails: Individual Bookings Range 1}, $[Column 11]@row, {Archived Jails: Individual Bookings Range 2}, <=DATE(2022, 9, 22), {Archived Jails: Individual Bookings ArchiveHisCom}, 1)


    Try this:

    =COUNTIFS({JAILS: Individual Bookings Arrest}, $[Column 11]@row, {JAILS: Individual Bookings ArrestDate}, >=DATE(2022, 9, 16), {JAILS: Individual Bookings ArrestDate}, <=DATE(2022, 9, 22), {JAILS: Individual Bookings HistoryCompleted}, 1) + COUNTIFS({Archived Jails: Individual Bookings Range 1}, $[Column 11]@row, {Archived Jails: Individual Bookings Range 2}, >=DATE(2022, 9, 16), {Archived Jails: Individual Bookings Range 2}, <=DATE(2022, 9, 22), {Archived Jails: Individual Bookings ArchiveHisCom}, 1)


    Now... for the checkbox column. Can you double check that the newly created ranges are pointing to the right place?

    {Archived Jails: Individual Bookings ArchiveHisCom}

    &

    {JAILS: Individual Bookings HistoryCompleted}


    If the ranges are correct, you could try using @cell = 1 as the criteria:

    =COUNTIFS({JAILS: Individual Bookings Arrest}, $[Column 11]@row, {JAILS: Individual Bookings ArrestDate}, >=DATE(2022, 9, 16), {JAILS: Individual Bookings ArrestDate}, <=DATE(2022, 9, 22), {JAILS: Individual Bookings HistoryCompleted}, @cell = 1) + COUNTIFS({Archived Jails: Individual Bookings Range 1}, $[Column 11]@row, {Archived Jails: Individual Bookings Range 2}, >=DATE(2022, 9, 16), {Archived Jails: Individual Bookings Range 2}, <=DATE(2022, 9, 22), {Archived Jails: Individual Bookings ArchiveHisCom}, @cell = 1)


    If none of this has helped... could you post screen captures of both sheets? (But block out sensitive data)

    Thanks!

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!