Formula to exclude filtered-out dates not within the last 7 Days

Question

Level 1

4 points

Formula to exclude filtered-out dates not within the last 7 Days

Could you please provide guidance on a formula or method that would NOT include filtered-out dates in column E? Please notice there are no dates within the last 7 days, hence the question.

Can the formula of =COUNTIF(DATE ADDED."≥"&TODAY-DURATION,7)) be modified to account for no dates in that column that are in the last 7 days? (see attached screenshot) If so, the value would be 0 and not 4.

MacBook Pro 16″, macOS 14.4

Posted on May 1, 2024 6:02 PM

Reply

Answer 1

Badunit

Level 7

26,314 points

May 1, 2024 7:25 PM in response to chadb427

If you have a filter that is hiding rows and you don't want to count any of the filtered out rows, you can use SUBTOTAL for your count.

=SUBTOTAL(103,E)

If you want to exclude dates that are within the past 7 days,

=COUNTIF(E,">"&TODAY()-7)

or it might be this next one if you are not including today as one of the 7 days

=COUNTIF(E,">="&TODAY()-7)

If you want to exclude dates that are earlier than the last 7 days,

=COUNTIF(E,"<"&TODAY()-6)

or

=COUNTIF(E,"<"&TODAY()-7)

Reply