How to count non blanks 5 years from the last non blank date
Requirement:
The user has a sheet with the column headers as dates by month and year. Column A is "Jan 2002", Column B is "Feb 2002" etc until Column HC "May 2019".
In Column HG the user has a the date of the last time my customer ordered "Aug 2007" for example.
For each month there is either a dollar amount or it is blank for each customer.
The user wants to count the number of times each customer ordered (non blank) within a 5 year period counting back from the last date they ordered.
If the last date they ordered was Oct 2006 then the user wants to count the number of months that the customer ordered between Oct 2001 - Oct 2006.
If within that time period there were 50 months were the customer ordered something then the user wants the result to show "50" in column HQ.
Solution:
COUNTIFS($A$1:$HC$1,">="&DATE(YEAR(HG2)-5,MONTH(HG2),1),$A$1:$HC$1,"<="&HG2,A2:HC2,">0")
Obtained from the OzGrid Help Forum.
Solution provided by Scott T.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to find last non blank cell & not affect split screen view |
| How to compare two columns in excel, inserting blank rows moving associated data |
| How to end a macro if cell balk or continue if populated |
| How to auto populate blank cells using VBA |
| How to remove blank lines from word document generated using Excel |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
