Formula to count consecutive values in a range

[Folander]

Hi Everyone,

I have you can help me, I need a formula to count the latestconsequetive times a location is in a range.

Example below:

<tbody> [TD="width: 77, bgcolor: transparent"] Jan [/TD]
[TD="width: 77, bgcolor: transparent"] Feb [/TD]
[TD="width: 77, bgcolor: transparent"] Mar [/TD]
[TD="width: 77, bgcolor: transparent"] Apr [/TD]
[TD="width: 77, bgcolor: transparent"] May [/TD]
[TD="width: 77, bgcolor: transparent"] June [/TD]
[TD="width: 77, bgcolor: transparent"] July [/TD]
[TD="width: 77, bgcolor: transparent"] Aug [/TD]
[TD="width: 77, bgcolor: transparent"] Sep [/TD]
[TD="width: 77, bgcolor: transparent"] Oct [/TD]
[TD="width: 77, bgcolor: transparent"] London [/TD]
[TD="width: 77, bgcolor: transparent"] London [/TD]
[TD="width: 77, bgcolor: transparent"] London [/TD]
[TD="width: 77, bgcolor: transparent"] Paris [/TD]
[TD="width: 77, bgcolor: transparent"] Paris [/TD]
[TD="width: 77, bgcolor: transparent"] London [/TD]
[TD="width: 77, bgcolor: transparent"] London [/TD]
[TD="width: 77, bgcolor: transparent"] London [/TD]
[TD="width: 77, bgcolor: transparent"] London [/TD]
[TD="width: 77, bgcolor: transparent"] London [/TD]
</tbody>

In an ideal world in a cell I would like to return for thecity line, a count of 5 for 5 month of London as the first 3 months should be disregardedas they have been broken by other cities.


Thanks for reading!

 

[oldbrewer]

[TABLE="width: 1216"]
<colgroup><col width="64" span="19" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Jan[/TD]
[TD="width: 64"]Feb[/TD]
[TD="width: 64"]Mar[/TD]
[TD="width: 64"]Apr[/TD]
[TD="width: 64"]May[/TD]
[TD="width: 64"]June[/TD]
[TD="width: 64"]July[/TD]
[TD="width: 64"]Aug[/TD]
[TD="width: 64"]Sep[/TD]
[TD="width: 64"]Oct[/TD]
[TD="width: 64"]Nov[/TD]
[TD="width: 64"]Dec[/TD]
[TD="width: 64"]Jan[/TD]
[TD="width: 64"]Feb[/TD]
[TD="width: 64"]Mar[/TD]
[TD="width: 64"]Apr[/TD]
[TD="width: 64"]May[/TD]
[TD="width: 64"]June[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]Paris[/TD]
[TD]Paris[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]Geneva[/TD]
[TD]Tokyo[/TD]
[TD]Tokyo[/TD]
[TD]Tokyo[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]using 2 helper rows the latest run for each city is marked[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]formula in A4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]=IF(ISERROR(MATCH(A2,B$2:$Z$2,0)),A3,0)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Folander]

Thanks Old Brewer, the issue is and I guess I should have mentioned it; I will have a list of 100+ staff with their locations over the course of the year, so I don’t think I can use your suggestion. If you can think of any other ideas that would be great.

 

[oldbrewer]

[TABLE="width: 1216"]
<colgroup><col width="64" span="19" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Jan[/TD]
[TD="width: 64"]Feb[/TD]
[TD="width: 64"]Mar[/TD]
[TD="width: 64"]Apr[/TD]
[TD="width: 64"]May[/TD]
[TD="width: 64"]June[/TD]
[TD="width: 64"]July[/TD]
[TD="width: 64"]Aug[/TD]
[TD="width: 64"]Sep[/TD]
[TD="width: 64"]Oct[/TD]
[TD="width: 64"]Nov[/TD]
[TD="width: 64"]Dec[/TD]
[TD="width: 64"]Jan[/TD]
[TD="width: 64"]Feb[/TD]
[TD="width: 64"]Mar[/TD]
[TD="width: 64"]Apr[/TD]
[TD="width: 64"]May[/TD]
[TD="width: 64"]June[/TD]
[/TR]
[TR]
[TD]fred[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]Paris[/TD]
[TD]Paris[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]Geneva[/TD]
[TD]Tokyo[/TD]
[TD]Tokyo[/TD]
[TD]Tokyo[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[/TR]
[TR]
[TD]bill[/TD]
[TD]Paris[/TD]
[TD]Paris[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]Geneva[/TD]
[TD]Tokyo[/TD]
[TD]Tokyo[/TD]
[TD]Bath[/TD]
[TD]Bath[/TD]
[TD]Bath[/TD]
[TD]Bath[/TD]
[TD]York[/TD]
[TD]York[/TD]
[TD]York[/TD]
[TD]York[/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]Bath[/TD]
[TD]Bath[/TD]
[TD]Bath[/TD]
[TD]Bath[/TD]
[TD]Paris[/TD]
[TD]Paris[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]Geneva[/TD]
[TD]Tokyo[/TD]
[TD]Tokyo[/TD]
[TD]York[/TD]
[TD]York[/TD]
[TD]York[/TD]
[TD]York[/TD]
[/TR]
[TR]
[TD]dave[/TD]
[TD]York[/TD]
[TD]York[/TD]
[TD]York[/TD]
[TD]York[/TD]
[TD]Bath[/TD]
[TD]Bath[/TD]
[TD]Bath[/TD]
[TD]Bath[/TD]
[TD]Paris[/TD]
[TD]Paris[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]Geneva[/TD]
[TD]Tokyo[/TD]
[TD]Tokyo[/TD]
[/TR]
[TR]
[TD]harry[/TD]
[TD]Paris[/TD]
[TD]Paris[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]Geneva[/TD]
[TD]Tokyo[/TD]
[TD]Tokyo[/TD]
[TD]York[/TD]
[TD]York[/TD]
[TD]York[/TD]
[TD]York[/TD]
[TD]Bath[/TD]
[TD]Bath[/TD]
[TD]Bath[/TD]
[TD]Bath[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]using this example - what do you want ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Folander]

[TABLE="width: 1216"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Jan
[/TD]
[TD="width: 64"]Feb
[/TD]
[TD="width: 64"]Mar
[/TD]
[TD="width: 64"]Apr
[/TD]
[TD="width: 64"]May
[/TD]
[TD="width: 64"]June
[/TD]
[TD="width: 64"]July
[/TD]
[TD="width: 64"]Aug
[/TD]
[TD="width: 64"]Sep
[/TD]
[TD="width: 64"]Oct
[/TD]
[TD="width: 64"]Nov
[/TD]
[TD="width: 64"]Dec
[/TD]
[TD="width: 64"]Jan
[/TD]
[TD="width: 64"]Feb
[/TD]
[TD="width: 64"]Mar
[/TD]
[TD="width: 64"]Apr
[/TD]
[TD="width: 64"]May
[/TD]
[TD="width: 64"]June
[/TD]
[/TR]
[TR]
[TD]fred
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]Paris
[/TD]
[TD]Paris
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]Geneva
[/TD]
[TD]Tokyo
[/TD]
[TD]Tokyo
[/TD]
[TD]Tokyo
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[/TR]
[TR]
[TD]bill
[/TD]
[TD]Paris
[/TD]
[TD]Paris
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]Geneva
[/TD]
[TD]Tokyo
[/TD]
[TD]Tokyo
[/TD]
[TD]Bath
[/TD]
[TD]Bath
[/TD]
[TD]Bath
[/TD]
[TD]Bath
[/TD]
[TD]York
[/TD]
[TD]York
[/TD]
[TD]York
[/TD]
[TD]York
[/TD]
[/TR]
[TR]
[TD]sid
[/TD]
[TD]Bath
[/TD]
[TD]Bath
[/TD]
[TD]Bath
[/TD]
[TD]Bath
[/TD]
[TD]Paris
[/TD]
[TD]Paris
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]Geneva
[/TD]
[TD]Tokyo
[/TD]
[TD]Tokyo
[/TD]
[TD]York
[/TD]
[TD]York
[/TD]
[TD]York
[/TD]
[TD]York
[/TD]
[/TR]
[TR]
[TD]dave
[/TD]
[TD]York
[/TD]
[TD]York
[/TD]
[TD]York
[/TD]
[TD]York
[/TD]
[TD]Bath
[/TD]
[TD]Bath
[/TD]
[TD]Bath
[/TD]
[TD]Bath
[/TD]
[TD]Paris
[/TD]
[TD]Paris
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]Geneva
[/TD]
[TD]Tokyo
[/TD]
[TD]Tokyo
[/TD]
[/TR]
[TR]
[TD]harry
[/TD]
[TD]Paris
[/TD]
[TD]Paris
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]London
[/TD]
[TD]Geneva
[/TD]
[TD]Tokyo
[/TD]
[TD]Tokyo
[/TD]
[TD]York
[/TD]
[TD]York
[/TD]
[TD]York
[/TD]
[TD]York
[/TD]
[TD]Bath
[/TD]
[TD]Bath
[/TD]
[TD]Bath
[/TD]
[TD]Bath
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]using this example - what do you want ?
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

To the right of the latest month, I would like to show whichcity Fred has consecutively spent the most months in. i.e.

<tbody> [TD="width: 193, bgcolor: transparent"] City [/TD]
[TD="width: 193, bgcolor: transparent"] Longest Duration (months) [/TD]
[TD="width: 193, bgcolor: transparent"] From [/TD]
[TD="width: 193, bgcolor: transparent"] To [/TD]
[TD="width: 193, bgcolor: transparent"] London [/TD]
[TD="width: 193, bgcolor: transparent"] 5 [/TD]
[TD="width: 193, bgcolor: transparent"] June 17 [/TD]
[TD="width: 193, bgcolor: transparent"] Oct 17 [/TD]
</tbody>

 

[ed bright]

I have a similar obstacle - need to count number of consecutive appearances equal to or greater than 6 times. Is it possible to use 'Countif' with "Sumproduct' or "Frequency" conditions? Any help would be much appreciated.

 

[oldbrewer]

[TABLE="width: 832"]
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]so we need to detect runs of 6 or more[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]in this case the eight sevens and the nine twos[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]2 simple helper columns count the sequences[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]and give you the run length[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]a thid helper triggers if run length is 6 or over[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

You can also get the answer without using helper columns. Try:

=SUM(IF(FREQUENCY(ROW(A2:A27),IF(A1:A26<>A2:A27,ROW(A1:A26)))>=6,1))
confirmed with Control+Shift+Enter.

Change the ranges to match your sheet.

 

[Jarodjp42]

@Folander

To the right of the latest month, I would like to show whichcity Fred has consecutively spent the most months in. i.e.

City

<tbody>
[TD="width: 193, bgcolor: transparent"] Longest Duration (months) [/TD]
[TD="width: 193, bgcolor: transparent"] From [/TD]
[TD="width: 193, bgcolor: transparent"] To [/TD]

[TD="width: 193, bgcolor: transparent"] London [/TD]
[TD="width: 193, bgcolor: transparent"] 5 [/TD]
[TD="width: 193, bgcolor: transparent"] June 17
[/TD]
[TD="width: 193, bgcolor: transparent"] Oct 17
[/TD]

</tbody>

I don't know if you still need the formula or if you have figured it out but this should do what you are looking for.

In Cell T2 enter this formula:

=INDEX(B2:S2,MODE(MATCH(B2:S2,B2:S2,0)))

In Cell U2 enter this formula: Remember its an array formula that needs to be enter by pressing Ctrl+Shift+Enter.

{=MAX(FREQUENCY(IF($A2:$S2=T2,COLUMN($A2:$T2)),IF($A2:$S2<>T2,COLUMN($A2:$S2))))}
**This is an array formula and needs to be entered by pressing Ctrl + Shift + Enter**

I don't have time to go through date formula but if no one has answered it for you I will come back when I have time. I hope this helps!