CODEWITHSUNDEEP
excelexcel-formula
Nick
Nick

Reputation: 47

Only counting the cells if the date is greater than another

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.

Upvotes: 0

Views: 761

Answers (1)

toucansame
toucansame

Reputation: 164

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.

Upvotes: 0

Related Questions