Countifs across multple sheets with a Checkbox

Haus

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,

Genevieve P.

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