Counting multiple columns with multiple conditions

Jeana

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

Sean Morgan

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

Sean Morgan

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