Excel, count how many consecutive months

Question

I have raw data where someone turns up multiple times each month, and over more than one month. I want to know how many months in a row (dating back from today) that person has a value of 0. How do I write a formula for that?

COUNTIF(s) PERSON IN A2 is found in RAWDATA and VALUE of SCORE COLUMN IN RAW DATA is ZERO and COUNT OF MONTHS IN A ROW FROM TODAY THAT IT IS ZERO"

Person A has score of Zero in May, April, in March and Febuary, so the result should be 4 (count from max date and back)

Name    Date    Score
Person A    1/10/2017   1
Person A    1/20/2017   1
Person A    1/30/2017   1
Person A    1/31/2017   1
Person A    2/10/2017   1
Person A    2/20/2017   0
Person A    2/24/2017   1
Person A    2/28/2017   0
Person A    3/10/2017   1
Person A    3/20/2017   1
Person A    3/31/2017   0
Person A    2017-04-31  0
Person A    4/10/2017   1
Person A    4/20/2017   1
Person A    4/28/2017   1
Person A    5/28/2017   0
Person A    5/10/2017   1
Person A    5/26/2017   1
Person A    5/10/2017   1
Person A    5/19/2017   1
Person A    5/30/2017   1
Person A    11/3/2016   1
Person A    11/11/2016  0
Person A    11/22/2016  1
Person A    12/9/2016   1
Person A    12/9/2016   1
Person A    12/19/2016  1
Person A    12/16/2016  1
Person A    2/10/2017   N/A
Person A    2/20/2017   1
Person A    2/24/2017   1
Person A    3/10/2017   1
Person A    3/20/2017   1
Person A    3/28/2017   N/A
Person A    4/20/2017   N/A
Person A    4/25/2017   N/A
Person A    5/30/2017   N/A
Person A    11/28/2016  N/A
Person A    12/6/2016   1

Answer 1

Definitely quite tricky.

My proposed solution is:

(a) Find the date D1 of the  latest zero score

(b) Going back month by month from D1, find the latest month which does not contain a zero score.

(a) is straightforward - use aggregate to find the latest date when person A has a score of zero

=AGGREGATE(14,6,RawData!$C$2:$C$100/((RawData!$A$2:$A$100=$A$2)*(RawData!$D$2:$D$100=0)),1)

(b) is more difficult. I had to develop an array of months then use COUNTIFS to see how many zero scores for person A fell in each month then use MATCH to see how far back the latest month with no zero scores was. So if you wanted to go back for a year say

=MATCH(0,COUNTIFS(RawData!$C$2:$C$100,"<"&DATE(YEAR($B2),MONTH($B2)-ROW($1:$12)+1,1),
RawData!$C$2:$C$100,">="&DATE(YEAR($B2),MONTH($B2)-ROW($1:$12),1),RawData!$A$2:$A100,$A2,RawData!$D$2:$D$100,0),0)

entered as an array formula using CtrlShiftEnter.

Note that my dates are in column C because I had to convert them into dd-mm-yyyy and my scores are in column D.