Excel Consecutive Formula

[mahmed1]

Hi -

I have 3 columns of data that holds

Date, contact number and answered which will have Yes or No (Data is sorted)
Im trying to figure out how to look at the number and get the consecutive number of times the word Yes appears before there is a No
So find the No in answered column for that contact number and count how many times above that there is a Yes or put a 1 next to it

And then it will need to reset and look at the next batch for that contact number - I can have helper columns if that helps

Eg say i have this scenario



Date….Contact No…Answered…Result to show

01/01…..0789………….Yes

01/01…..0789………….Yes

01/01…..0789………….No…….1

01/01…..0789………….Yes……Reset here as it was answered so put a 2

01/01…..0789………….Yes

01/01…..0789………….No…….1

01/01…..0789………….No…….1

01/01…..0789………….No…….1

01/01…..0789………….Yes……Reset here as it was answered so put a 2

01/01…..0789………….No

01/01…..0789………….No

01/01…..0789………….No



No further answered calls so leave blank so essentially im trying to find all the answered where it shows yes and then put a 1 for all the No above it



Hope this makes sense



I tried this with no luck



=IF(B2<>B1,"",IF(C2="No","",IF(C2="Yes",IF(COUNTIF(INDEX($C$2:C2,MATCH(B2,$B$2:B2,0)):C2,"No")>0,2,1),"")))

So essentially how many No were there before a yes - put a 1 in the cell and 2 to denote it resets there ie answered at that point where it shows yes..

Result should be like screenshot

 

[mahmed1]

Thank you - ill give that a go