Only counting the cells if the date is greater than another

0
votes

Photo of spreadsheet

I'm looking to the count the cells(months) after a customer first orders or as far back as Apr 2017(column v). I can currently do this when I know the range but I would like it to start on it's own at the first occurrence if it is after Apr 2017. I've included an image to help. For example: if they only been a customer since Feb 2018 I only want to count them as 2 months not since April 2017.

This is my current formula for counting a row that goes back as far as April 2017, =SUM(COUNTIF(V2:AG2,""),(COUNTIF(V2:AG2,">0")))

I have the month of their first order in column AL but I can't seem to get a function working properly where I compare AL to V.

Any help is greatly appreciated. Thank you community.

1
excelexcel-formula
What is it about your formula that's not working? Your Month of First Order (column AL) seems to agree with the data on each row such that your formula would work. Which row is an example that doesn't work? – PeterT

1 Answers

0
votes

Try this: =IF(MATCH(TRUE,INDEX((A2:AG2<>0),0),0)<COLUMN(V2),COLUMN(AG2)-COLUMN(V2)+1,COLUMN(AG2)-MATCH(TRUE,INDEX((A2:AG2<>0),0),0)+1)

This says that if the first non-blank cell in A2:AG2 is before V2 then give the number of months between V2 & AG2, otherwise, count the number of months between the first non-blank cell after column V and column AG.

I used the COLUMN function in case you want to make changes to the periods you're looking at later. LMK if this works for you.