CountIfs Date Range and Type

Silvia Frei

Hi,

I am trying to calculate the number of tasks completed in the last 30 days which also have been tagged "WO" in the Type column. I get a unparseable error using this formula, any suggestions?

=COUNTIFS(Type:Type, HAS(@cell"WO"); [Date Marked Complete]:[Date Marked Complete], >TODAY(-30))

Silvia

Paul Newcome

Yes. COUNTIFS essentially has the AND function built into it, so saying

Type:Type, HAS(@cell, "WO"),Type:Type, HAS(@cell, "MSR")

basically says that it has to have "WO" AND "MSR" in the cell.

To work in an OR statement into a COUNTIFS, you would use...

=COUNTIFS(range:range, OR(argument1, argument2, argument3), .................)

Which would mean your formula would look something like this...

=COUNTIFS(Type:Type, OR(HAS(@cell, "WO"), HAS(@cell, "MSR"), HAS(@cell, "QBR")), Chunk:Chunk, HAS(@cell, "Prod & DevOps"), [Date Marked Complete]:[Date Marked Complete], >TODAY(-30))

Silvia Frei

Awesome, thanks so much for the explanation! Really appreciated :)

Paul Newcome

If "WO" is the only thing in the cell, then you shouldn't need the HAS function.

=COUNTIFS(Type:Type, "WO", [Date Marked Complete]:[Date Marked Complete], > TODAY(-30))

If you wanted to maintain the HAS function, you would need a comma between @cell and "WO" and you would need to replace the semicolon after the HAS function with a comma.

=COUNTIFS(Type:Type, HAS(@cell, "WO"), [Date Marked Complete]:[Date Marked Complete], >TODAY(-30))

Silvia Frei

Thanks Paul, works perfectly!

If I wanted to add more criteria under the Type column and count any instances when either WO, MSR or QBR are listed how could I introduce OR into the formula? The one below works but it's not counting correctly.

=COUNTIFS(Type:Type, HAS(@cell, "WO"),Type:Type, HAS(@cell, "MSR"), Type:Type, HAS(@cell, "QBR"), Chunk:Chunk, HAS(@cell, "Prod & DevOps"), [Date Marked Complete]:[Date Marked Complete], >TODAY(-30))

Paul Newcome

Yes. COUNTIFS essentially has the AND function built into it, so saying

Type:Type, HAS(@cell, "WO"),Type:Type, HAS(@cell, "MSR")

basically says that it has to have "WO" AND "MSR" in the cell.

To work in an OR statement into a COUNTIFS, you would use...

=COUNTIFS(range:range, OR(argument1, argument2, argument3), .................)

Which would mean your formula would look something like this...

=COUNTIFS(Type:Type, OR(HAS(@cell, "WO"), HAS(@cell, "MSR"), HAS(@cell, "QBR")), Chunk:Chunk, HAS(@cell, "Prod & DevOps"), [Date Marked Complete]:[Date Marked Complete], >TODAY(-30))

Silvia Frei

Awesome, thanks so much for the explanation! Really appreciated :)

Paul Newcome

Happy to help! 👍️

Melissa Torrez

@Paul Newcome I have a similar request. Can you help with this formula?

=COUNTIFS([Report Status]:[Report Status], OR(HAS(@cell, "Not Started"), HAS(@cell, "Not Started", Finish:Finish, <=TODAY(30))))

I am trying to count tasks that have a status of Not Started or In Progress and are in the next 30 days

Genevieve P.

Hi @Melissa Torrez

It sounds like you have two different things you're searching for. In this instance I would build 2 formulas and add them together with +, like so:

=COUNTIFS([Report Status]:[Report Status], HAS(@cell, "Not Started")) + COUNTIFS([Report Status]:[Report Status], HAS(@cell, "In Progress"), Finish:Finish, <= TODAY(30))

Cheers,

Genevieve

Melissa Torrez

@Genevieve P. somehow that did not return the correct result. Any other thoughts?

Genevieve P.

Hi @Melissa Torrez

Are you able to post a screen capture of your sheet, but block out sensitive data?

We only need the HAS function if your Report Status column is multi-select. I wonder if we could simplify this by taking that out:

=COUNTIFS([Report Status]:[Report Status], "Not Started") + COUNTIFS([Report Status]:[Report Status], "In Progress", Finish:Finish, <= TODAY(30))

If you're getting the wrong number, try each formula individually:

=COUNTIFS([Report Status]:[Report Status], "Not Started") 

=COUNTIFS([Report Status]:[Report Status], "In Progress", Finish:Finish, <= TODAY(30))

Keep in mind that this will take into account any "In Progress" tasks where the finish date is less than 30 days from now, including any date in the past as well.

Paul Newcome

@Melissa Torrez It looks like you just forgot to close out the second HAS and the OR functions (as well as double typed "Not Started" instead of "In Progress"). I also second @Genevieve P.'s comment that we only need the HAS functions if it is a multi-select dropdown type that could have multiple selections made in the same cell.

Lets remove the HAS functions completely and just close out that OR function.

=COUNTIFS([Report Status]:[Report Status], OR(@cell = "In Progress", @cell = "Not Started"), Finish:Finish, <=TODAY(30))

Melissa Torrez

@Paul Newcome and @Genevieve P. That works, but the number it is returning is still incorrect. When I filter on finish date in the next 30 days and filter on report status in progress, not started I get 13 as the count. The formula returns the correct number when I take out @cell=Not Started, but when I add it back to the formula I get 28 as the count.

Paul Newcome

Your filter isn't set up exactly like your formula though. Your filter is in the next 30 days, but your formula is looking for dates less than TODAY + 30. This means the formula is also going to include dates in the past whereas your filter will not.

For your formula to mimic the filter, you would need something more like this criteria for your date range:

=COUNTIFS([Report Status]:[Report Status], OR(@cell = "In Progress", @cell = "Not Started"), Finish:Finish, AND(@cell>=TODAY(), @cell<=TODAY(30)))