CODEWITHSUNDEEP
exceldateexcel-formulacountif
Andice
Andice

Reputation: 1

COUNTIF function using a date range with ">"&TODAY()-N)

I am trying to count all date instances for the past seven days not including today. The formula I am using is: 

=COUNTIF($H$11:$H$501,">"&TODAY()-7)

In testing this formula, any dates equal to TODAY() are being selected. I do not want to have any dates equal to today to be selected.

Thank you for your help.

Upvotes: 0

Views: 2437

Answers (2)

JvdV
JvdV

Reputation: 75840

I was thinking about a SUMPRODUCT approach first to create an array of legit dates to use withing an OR construct, but we can simply create that ourselves:

=SUM(COUNTIF($H$11:$H$501,TODAY()-{1,2,3,4,5,6}))

If you want to be more flexible:

=SUMPRODUCT(COUNTIF($H$11:$H$501,TODAY()-ROW(1:6)))

Do notice that @BigBen's answer will be the more common approach which you should adopt!

Upvotes: 1

BigBen
BigBen

Reputation: 49998

Use COUNTIFS and add an additional criterion, that the dates are less than TODAY:

=COUNTIFS($H$11:$H$501,">"&TODAY()-7,$H$11:$H$501,"<"&TODAY())

Upvotes: 1

Related Questions