# OzGrid

### 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.

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

stars (0 Reviews)