COUNTIFS

Laura2022

I'm having trouble getting a correct count of "in progress" tasks due in the next 14 days. I'm using =COUNTIFS([Completion Date]:[Completion Date], <=TODAY(+14), Status:Status, "in progress"), which should return a 0 but is, instead returning 20:

I've used COUNTIFS formulas for other summary fields with no problem but this one is puzzling me.

Thank you for any light you can shed on this matter.

Regards,

Laura Howard

Nick Korna

Hi @Laura2022 ,

The formula is looking at "In Progress" tasks which have completion dates equal or less than 14 days from Today's date. At a guess your filter is correctly taking out the ones which are in the past (overdue), but the formula is not. If you try:

=COUNTIFS(Status:Status, "In Progress", [Completion Date]:[Completion Date], <=TODAY(14), [Completion Date]:[Completion Date], >=TODAY())

if you want today included (remove the final equals sign if you don't) then this should hopefully give you the result you're after.

Let me know if this is correct or if I've made an incorrect assumption somewhere along the way!

Nick Korna

Hi @Laura2022 ,

The formula is looking at "In Progress" tasks which have completion dates equal or less than 14 days from Today's date. At a guess your filter is correctly taking out the ones which are in the past (overdue), but the formula is not. If you try:

=COUNTIFS(Status:Status, "In Progress", [Completion Date]:[Completion Date], <=TODAY(14), [Completion Date]:[Completion Date], >=TODAY())

if you want today included (remove the final equals sign if you don't) then this should hopefully give you the result you're after.

Let me know if this is correct or if I've made an incorrect assumption somewhere along the way!

Laura2022

It worked! Thank you so much!