# SOLVED - COUNTIFS Problem

Options
edited 12/09/19

Hi,

I'm trying to return a status within a range when the finish date is within 7 days, I've tried a few alternatives with the latest below (using @cell) which I know isn't correct but I'm hoping I'm close. Any advice would be greatly appreciated.

=COUNTIFS({RangeStatus}, [Not Started]1, {RangeFinish}, @cell <=TODAY(), @cell > TODAY(7)))

Tags:

• Employee
Options

Hello,

I noticed a few things here:

First off, you don't need @cell, you can type your criteria directly after the comma: =COUNTIFS({RangeStatus}, [Not Started]1, {RangeFinish}, <=TODAY())

COUNTIFS will only count cells that meet all criteria. I don't believe that a date can both be less than or equal to the current date and also greater than the current date plus 7 days.

If you're wanting to add together the count of dates that meet the [Not Started]1 criteria and only ONE of the two date criteria, try adding COUNTIFS statements together:

=COUNTIFS({RangeStatus}, [Not Started]1, {RangeFinish}, <=TODAY()) + COUNTIFS({RangeStatus}, [Not Started]1, {RangeFinish}, >TODAY(7))

• Options

Hi Shaine,

Thanks for your response, my original formula was

=COUNTIFS({RangeFinish}, <=TODAY(7), {RangeStatus}, \$[Not Started]\$1)

which I though would work but it didn't. I tried the @cell as an option but that obviously wouldn't work.

Smartsheet support have actually come back to me with this example formula:

=COUNTIFS(Enddate:Enddate, "not started", DueTime:DueTime, >TODAY(), DueTime:DueTime, <=TODAY(7))

but alas that doesn't return the correct value - I have 5 rows set at "Not Started" that have Finish dates due within 7 days but the above formula only returned a value of 1.

I'm baffled.

• Options

Solved by adding an extra =.

=COUNTIFS(Enddate:Enddate, "not started", DueTime:DueTime, >=TODAY(), DueTime:DueTime, <=TODAY(7))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!