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.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!