In article <
[email protected]>, Philip Herlihy wrote...
In article <[email protected]>, Emma Rayment wrote...
Hi there,
I am looking for help to figure out the formula that would help me count how many rows in a column have a date that is within the next 90 days?
Column: Renewal Date
Row: 1/2/23
I want to have a total sum at the bottom that will count the number of accounts I have that are up for renewal within the next 90 days.
Thanks
If you want to count the number of cells in a range which meet a particular criterion, then the COUNTIF formula seems appropriate. (For more than one criterion, use COUNTIFS.)
For your criterion, you'd want to compare the date in the cell with the resut of the TODAY() function.
Have a look at this:
https://www.ablebits.com/office-addins-blog/excel-countif-function-examples/ #countif-dates
The key trick appears to be to use the '&' character to build the criterion, so that elements like TODAY(), and numeric constants, are evaluated before the criterion is assembled. So you'd have something like:
=COUNTIF(A1:A999,"<="&TODAY()+90)
Try it with these dates:
13/01/2023
20/01/2023
27/01/2023
03/02/2023
10/02/2023
17/02/2023
24/02/2023
03/03/2023
10/03/2023
17/03/2023
24/03/2023
31/03/2023
07/04/2023
14/04/2023
21/04/2023
28/04/2023
05/05/2023
12/05/2023
19/05/2023
26/05/2023
You should get 13 if you run it around now.
--
Phil, London
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)