Count non blanks 5 years from the last non blank date

[mljohn]

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/...n-blanks-5-years-from-the-last-non-blank-date

 

[Scott T]

Does this do what you want?

=COUNTIFS($A$1:$HC$1,">="&DATE(YEAR(HG2)-5,MONTH(HG2),1),$A$1:$HC$1,"<="&HG2,A2:HC2,">0")

 

[mljohn]

Does this do what you want?

=COUNTIFS($A$1:$HC$1,">="&DATE(YEAR(HG2)-5,MONTH(HG2),1),$A$1:$HC$1,"<="&HG2,A2:HC2,">0")

Perfect! Thanks for the help.

Matt

 

[Scott T]

You're welcome