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:

Jan	Feb	Mar	Apr	May	June	July	Aug	Sep	Oct
London	London	London	Paris	Paris	London	London	London	London	London

<tbody> </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]

Jan	Feb	Mar	Apr	May	June	July	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	June
London	London	London	Paris	Paris	London	London	London	London	London	Geneva	Tokyo	Tokyo	Tokyo	London	London	London	London
1	2	3	1	2	1	2	3	4	5	1	1	2	3	1	2	3	4
0	0	0	0	2	0	0	0	0	0	1	0	0	3	0	0	0	4
				using 2 helper rows the latest run for each city is marked
				formula in A4
				=IF(ISERROR(MATCH(A2,B$2:$Z$2,0)),A3,0)

<colgroup><col width="64" span="19" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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]

Jan

Feb

Mar

Apr

May

June

July

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Apr

May

June

fred

London

London

London

Paris

Paris

London

London

London

London

London

Geneva

Tokyo

Tokyo

Tokyo

London

London

London

London

bill

Paris

Paris

London

London

London

London

London

Geneva

Tokyo

Tokyo

Bath

Bath

Bath

Bath

York

York

York

York

sid

Bath

Bath

Bath

Bath

Paris

Paris

London

London

London

London

London

Geneva

Tokyo

Tokyo

York

York

York

York

dave

York

York

York

York

Bath

Bath

Bath

Bath

Paris

Paris

London

London

London

London

London

Geneva

Tokyo

Tokyo

harry

Paris

Paris

London

London

London

London

London

Geneva

Tokyo

Tokyo

York

York

York

York

Bath

Bath

Bath

Bath

using this example - what do you want ?

<colgroup><col width="64" span="19" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Folander]

Jan

Feb

Mar

Apr

May

June

July

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Apr

May

June

fred

London

London

London

Paris

Paris

London

London

London

London

London

Geneva

Tokyo

Tokyo

Tokyo

London

London

London

London

bill

Paris

Paris

London

London

London

London

London

Geneva

Tokyo

Tokyo

Bath

Bath

Bath

Bath

York

York

York

York

sid

Bath

Bath

Bath

Bath

Paris

Paris

London

London

London

London

London

Geneva

Tokyo

Tokyo

York

York

York

York

dave

York

York

York

York

Bath

Bath

Bath

Bath

Paris

Paris

London

London

London

London

London

Geneva

Tokyo

Tokyo

harry

Paris

Paris

London

London

London

London

London

Geneva

Tokyo

Tokyo

York

York

York

York

Bath

Bath

Bath

Bath

using this example - what do you want ?

<tbody>
</tbody>

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

City	Longest Duration (months)	From	To
London	5	June 17	Oct 17

<tbody> </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]

3
3	1
3	2	2
7	1
7	2					so we need to detect runs of 6 or more
7	3
7	4					in this case the eight sevens and the nine twos
7	5
7	6
7	7					2 simple helper columns count the sequences
7	8	8	yes			and give you the run length
9	1
9	2					a thid helper triggers if run length is 6 or over
9	3	3
2	1
2	2
2	3
2	4
2	5
2	6
2	7
2	8
2	9	9	yes
4	1
4	2
4	3	3

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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	Longest Duration (months)	From	To
London	5	June 17	Oct 17

<tbody>
</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!