Count non blanks 5 years from the last non blank date

  • 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…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.

  • 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