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,
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!