COUNTIFS failing to accurately count

Laura2022

I'm trying to show how many projects are requested in the next 30 days by department:

I’ve gone through the videos and I believe I have set up my formulas correctly; however, the counts are incorrect.

I'm using the formula =COUNTIFS({Department}, Department@row, {Due date}, <=TODAY(+30))

It's probably something minor that I've overlooked but I cannot seem to get this formula correct. Please help.

Thank you,

Laura

Genevieve P.

Hi @Laura2022

Instead of changing the formula to only = TODAY(), Paul was meaning to add in an additional criteria so you have a range of dates to look through.

Ex:

=COUNTIFS({Department}, Department@row, {Due date}, <=TODAY(+30), {Due date}, >=TODAY())

This will look for any dates that are less than or equal to 30 days from now, but only if they are also greater than or equal to Today.

I also notice that your Department is a multi-select column. In this instance you'll want to use the HAS Function when you're looking for your Department, to see if the cell has this value along with other selections.

Try:

=COUNTIFS({Department}, HAS(@cell, Department@row), {Due date}, <=TODAY(+30), {Due date}, >=TODAY())

Cheers,

Genevieve

Paul Newcome

Are you able to provide a screenshot of the source data? I do see that you are looking for anything that is less than or equal to TODAY(30). This means a date from 12 years ago will also be counted because it is less than the date that is 30 days from today.

Try adding in another range criteria set where the range is the same date range and the criteria is greater than or equal to TODAY().

Laura2022

Thank you for your quick response. Here's a screenshot of my source datasheet:

I tried using greater than or equal to TODAY() but the Requested column numbers are still off; if I set it to equal to TODAY(), I get a zero.

Genevieve P.

Hi @Laura2022

Instead of changing the formula to only = TODAY(), Paul was meaning to add in an additional criteria so you have a range of dates to look through.

Ex:

=COUNTIFS({Department}, Department@row, {Due date}, <=TODAY(+30), {Due date}, >=TODAY())

This will look for any dates that are less than or equal to 30 days from now, but only if they are also greater than or equal to Today.

I also notice that your Department is a multi-select column. In this instance you'll want to use the HAS Function when you're looking for your Department, to see if the cell has this value along with other selections.

Try:

=COUNTIFS({Department}, HAS(@cell, Department@row), {Due date}, <=TODAY(+30), {Due date}, >=TODAY())

Cheers,

Genevieve

Laura2022

Thank you both. I was able to successfully recalculate the data and it's reflecting what it should!

Genevieve P.

Wonderful! I'm glad we could help 🙂