Help with Date Range on a formula

Hi,

The first thing you should do is make sure all the dates in column J are proper date numbers. At the moment some are text. e.g.J84, J64, J63
You can test whether a cell is a number by using =ISNUMBER(J84).
This is important since unless Excel is working with date numbers any analysis like the count of records in the last x days won't be accurate.
The easiest way to convert them is to put a zero in a blank cell somewhere, Copy it, select J5:J83 and use the Paste Special Add functionality.

That will convert text dates to numbers and you can then reformat all the numbers so that they present as dates.

With your data there are no records within 7 days of today, but as an example let's assume you want the count of records within the last 14 days.
In say C89 enter the value 14. Then in C91 enter

Formula:

=COUNTIFS($J$5:$J$87,">="&TODAY()-$C$89)

which will give you the number of quotes = 24.


In C92 enter

Formula:

=COUNTIFS(M5:M87,"Converted",$J$5:$J$87,">="&TODAY()-$C$89)

which will result in 8 conversions in the last 14 days.

Use similar pairs of criteria inother COUNTIFS

See attached.