Count non blanks 5 years from the last non blank date

mljohn

Board Regular
Joined
Aug 26, 2004
Messages
170
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.ozgrid.com/forum/forum/help-forums/excel-general/1218474-count-non-blanks-5-years-from-the-last-non-blank-date
 
Last edited:

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,606
Office Version
365, 2016
Platform
Windows
Does this do what you want?
Code:
=COUNTIFS($A$1:$HC$1,">="&DATE(YEAR(HG2)-5,MONTH(HG2),1),$A$1:$HC$1,"<="&HG2,A2:HC2,">0")
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,606
Office Version
365, 2016
Platform
Windows
You're welcome
 

Forum statistics

Threads
1,085,542
Messages
5,384,332
Members
401,887
Latest member
Somesh

Some videos you may like

This Week's Hot Topics

Top