# Count non blanks 5 years from the last non blank date

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• I have 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 I have 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.

I want 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 I want 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 I want the result to show "50" in column HQ.

Thanks for the help

https://www.mrexcel.com/forum/…ank-date.html#post5276763

• Attach a sample workbook
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

• Here is the sample. Ultimately I want to know the 5 year average value of my customers and the Lifetime value.
[ATTACH]n1218490[/ATTACH]

## Files

• Scott T at Mr. Excel came up with the solution.
COUNTIFS(\$A\$1:\$HC\$1,">="&DATE(YEAR(HG2)-5,MONTH(HG2),1),\$A\$1:\$HC\$1,"<="&HG2,A2:HC2,">0") Thanks for your support Matt

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!