COUNTIFS X or Y or Z (Check boxes)
Hi can I get assistance in writing a formula for the following:
=COUNTIFS({CSN}, ([NOV 5]:[NOV 5], or 1, [NOV 6]:[NOV 6], 1 ,or [NOV 7]:[NOV 7], 1)
Best Answer
-
To count entries in Smartsheet based on multiple columns with checkboxes, where you want to count if any of the specified dates (e.g., Nov 5, Nov 6, Nov 7) have a checkbox marked, you can use a combination of
COUNTIFS
andOR
logic within a formula.Since Smartsheet doesn’t support
OR
directly insideCOUNTIFS
, you’ll need to sum multipleCOUNTIFS
conditions separately and add them together. Here’s how you could set this up:Formula
excelCopy code=COUNTIFS({CSN}, 1, [NOV 5]:[NOV 5], 1) + COUNTIFS({CSN}, 1, [NOV 6]:[NOV 6], 1) + COUNTIFS({CSN}, 1, [NOV 7]:[NOV 7], 1)
Explanation
- Each
COUNTIFS
counts rows where{CSN}
is 1 and where each specified date column (Nov 5, Nov 6, or Nov 7) is also marked as 1 (checked). - Adding
COUNTIFS
results together simulates anOR
condition across the Nov 5, Nov 6, and Nov 7 columns.
This formula will give you the total count of rows where
{CSN}
is 1, and at least one of the specified date checkboxes (Nov 5, Nov 6, or Nov 7) is checked.Let me know if you need any more assistance!
Murphy Carlson
DigitalRadius, Smartsheet Platinum Partner
mcarlson@digitalradius.com
- Each
Answers
-
To count entries in Smartsheet based on multiple columns with checkboxes, where you want to count if any of the specified dates (e.g., Nov 5, Nov 6, Nov 7) have a checkbox marked, you can use a combination of
COUNTIFS
andOR
logic within a formula.Since Smartsheet doesn’t support
OR
directly insideCOUNTIFS
, you’ll need to sum multipleCOUNTIFS
conditions separately and add them together. Here’s how you could set this up:Formula
excelCopy code=COUNTIFS({CSN}, 1, [NOV 5]:[NOV 5], 1) + COUNTIFS({CSN}, 1, [NOV 6]:[NOV 6], 1) + COUNTIFS({CSN}, 1, [NOV 7]:[NOV 7], 1)
Explanation
- Each
COUNTIFS
counts rows where{CSN}
is 1 and where each specified date column (Nov 5, Nov 6, or Nov 7) is also marked as 1 (checked). - Adding
COUNTIFS
results together simulates anOR
condition across the Nov 5, Nov 6, and Nov 7 columns.
This formula will give you the total count of rows where
{CSN}
is 1, and at least one of the specified date checkboxes (Nov 5, Nov 6, or Nov 7) is checked.Let me know if you need any more assistance!
Murphy Carlson
DigitalRadius, Smartsheet Platinum Partner
mcarlson@digitalradius.com
- Each
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!