How can use the Countifs function with the Today function in the formula?

John Sisounthone

Hello,

I am not sure what I did wrong in the formula that I have created. When I physically count it, I would get 23 but the formula spits out zero. I want the formula to report the number of tasks that was within the last 30 days.

Here is my formula: =COUNTIF({Ready For Upload Range 4}, {Ready For Upload Range 2} <= TODAY(-30))

I am creating the formula on a separate sheet than the one with the information. I can post the two test sheets that I am currently using to figure this out before applying it to the actual sheets.

Thank you.

Genevieve P.

Hi @John Sisounthone

If you're looking for a range (between 30 days), then you'll need to add in a Start of the range and an End of the range. You currently just state the Start of the range (30 days ago), but there's no end for this (today).

Here's how you would set up a cross-sheet COUNTIFS (note the S to make it plural) to look through a date range:

=COUNTIFS({Ready For Upload Range 2}, >= TODAY(-30), {Ready For Upload Range 2}, <= TODAY())

Note that I swapped around your <= and >= as well.

>= TODAY(-30) checks to see if the date is greater than 30 days ago, instead of <= TODAY(-30) which looks to see if the date is less than 30 days ago (as in, earlier than 30 days ago).

If this doesn't work for you, it would be helpful to see a screen capture of the sheet you're looking into, with an explanation of any other criteria you're looking to count. I'm unclear as to what your initial range, Range 4, is, so that would be useful to know as well.

Thanks!

Genevieve

Genevieve P.

Hi @John Sisounthone

If you're looking for a range (between 30 days), then you'll need to add in a Start of the range and an End of the range. You currently just state the Start of the range (30 days ago), but there's no end for this (today).

Here's how you would set up a cross-sheet COUNTIFS (note the S to make it plural) to look through a date range:

=COUNTIFS({Ready For Upload Range 2}, >= TODAY(-30), {Ready For Upload Range 2}, <= TODAY())

Note that I swapped around your <= and >= as well.

>= TODAY(-30) checks to see if the date is greater than 30 days ago, instead of <= TODAY(-30) which looks to see if the date is less than 30 days ago (as in, earlier than 30 days ago).

If this doesn't work for you, it would be helpful to see a screen capture of the sheet you're looking into, with an explanation of any other criteria you're looking to count. I'm unclear as to what your initial range, Range 4, is, so that would be useful to know as well.

Thanks!

Genevieve

John Sisounthone

Hi @Genevieve P

Thank you for your help. Your formula worked. I needed to add the end date.

Kind Regards,

Johnathon

Genevieve P.

Glad to hear that it worked for you!