Counting multiple columns with multiple conditions
I have a working formula that counts if a task is not complete and due in a specific week:
=COUNTIFS([Calc if Done]@row, =0, [End Date]@row, AND(@cell > TODAY(+7), @cell <= TODAY(+14)))
This currently returns a 0 if false and 1 if true. Then can sum the Tasks due for particular week.
I need to add another condition to this formula that COUNTS if the Task Owner OR Additional Resource are any of 4 different possibilities. I'm struggling with how to do this one formula and thinking it would need to be parsed out into several different column/formulas. I have started breaking it down into several formulas and I am comfortable doing it that way. I'm looking for a cleaning (less columns and formulas) solution if possible.
Is it possible in one formula to determine for each row:
IF Calc if done = 0
AND End Date is between two dates
I HAVE THE FORMULA WORKING UP TO THIS POINT. I ALSO WANT THE FORMULA TO...
COUNTIF Task Owner OR Additional Resource = any of 4 different possibilities.
Hoping one of the genius formula folks can guide me on this one.
Thanks!!
Jeana
Best Answer

Hello @Jeana ,
So I've created a possible solution for this, but please let me know if I have missed anything, and I'd be more than happy to work with you on creating something more accurate.
Nonetheless, here's what I was able to come up with.
The Formula I used was: =COUNTIFS([Calc if Done]@row, =0, [End Date]@row, AND(@cell > TODAY(+7), [End Date]@row <= TODAY(+14)), Contacts@row, OR(Contacts@row = "Sean Morgan", @cell = "Test Sean", @cell = "Test", @cell = "Sean Test"))
Here's the outcome:
Let me know fi you have any questions!
Regards
Sean
Answers

Hello @Jeana ,
So I've created a possible solution for this, but please let me know if I have missed anything, and I'd be more than happy to work with you on creating something more accurate.
Nonetheless, here's what I was able to come up with.
The Formula I used was: =COUNTIFS([Calc if Done]@row, =0, [End Date]@row, AND(@cell > TODAY(+7), [End Date]@row <= TODAY(+14)), Contacts@row, OR(Contacts@row = "Sean Morgan", @cell = "Test Sean", @cell = "Test", @cell = "Sean Test"))
Here's the outcome:
Let me know fi you have any questions!
Regards
Sean
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!