SOLVED - COUNTIFS Problem

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)))

Find more posts tagged with

Formulas

Comments

Shaine Greenwood

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))

Outofmydepth

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.

Outofmydepth

Solved by adding an extra =.

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

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…