COUNTIFS- date range in the last 7 days that meets criteria from two other coulmns also

edited 12/09/19 in Smartsheet Basics

Hi All,

Looking for a hand with a formula that will return a count for items that were in the last 7 days. The formula is pulling from another sheet for this data. Looking for the count of items, assigned to a specific person, are closed and closed in the last seven days.

Here is the formula I have but it is returning the wrong count. if I go the main page an filter it or build a report with the same info.

=COUNTIFS({Assigned To}, [Assigned To]6, {Status}, "Closed", {Closed Date}, <=TODAY(), {Closed Date}, <=TODAY(8))


  • Shaine Greenwood

    Hi Glen,

    Looks like you have two instances of the TODAY function in your formula, you only need one to accomplish what you want.

    =COUNTIFS({Assigned To}, [Assigned To]6, {Status}, "Closed", {Closed Date}, <=TODAY(7))

    If that doesn't work, try flipping the criteria a bit:

    =COUNTIFS({Assigned To}, [Assigned To]6, {Status}, "Closed", {Closed Date}, >=TODAY(-7))

    More on COUNTIFS here: