Counting consecutive listed names

These formulas are complex and if you need to do this over a year period the formulas will be quite long.

Data Range

	L	M	N	O
2	Coach		Celebrate
3	Joe	1	Kylee	1
4	Jess	3	Avril	1
5	Tim	3	Rex	1
6	Rita	2	Emily	1
7	Matt	1	Jeff	3
8	Khan	1	Vita	3
9	Chris	1	Rob	2
10	Steve	3	Bex	2
11	Tom	3	Rachel	2
12	Steph	3	Pam	2
13	------	------	------	------

This array formula** entered in M3 and copied down:

=MAX(FREQUENCY(IF(COUNTIF(INDIRECT({"C4:C13","E4:E13","G4:G13"}),L3)=1,COLUMN(A:C)),IF(COUNTIF(INDIRECT({"C4:C13","E4:E13","G4:G13"}),L3)<>1,COLUMN(A:C))))

This array formula** entered in O3 and copied down:

=MAX(FREQUENCY(IF(COUNTIF(INDIRECT({"D4:D13","F4:F13","H4:H13"}),N3)=1,COLUMN(A:C)),IF(COUNTIF(INDIRECT({"D4:D13","F4:F13","H4:H13"}),N3)<>1,COLUMN(A:C))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Originally Posted by vidhyakrr

M Column: =IF(COUNTIF($C$4:$C$13,L3)+COUNTIF($E$4:$E$13,L3)+COUNTIF($G$4:$G$13,L3)=2,1,COUNTIF($C$4:$C$13,L3)+COUNTIF($E$4:$E$13,L3)+COUNTIF($G$4:$G$13,L3))

N Column: =IF(COUNTIF($D$4:$D$13,N3)+COUNTIF($F$4:$F$13,N3)+COUNTIF($H$4:$H$13,N3)=2,1,COUNTIF($D$4:$D$13,N3)+COUNTIF($F$4:$F$13,N3)+COUNTIF($H$4:$H$13,N3))

Where the counts should be 2 your formulas are returning 1.

Another way.

For coaches in M3 array enter this formula (Ctrl + Shift + Enter) and fill down.

Formula:

Please Login or Register  to view this content.

For celebrates in O3 array enter this and fill down.

Formula:

Please Login or Register  to view this content.

	L	M	N	O
2	Coach		Celebrate
3	Joe	1	Kylee	1
4	Jess	3	Avril	1
5	Tim	3	Rex	1
6	Rita	2	Emily	1
7	Matt	1	Jeff	3
8	Khan	1	Vita	3
9	Chris	1	Rob	2
10	Steve	3	Bex	2
11	Tom	3	Rachel	2
12	Steph	3	Pam	2

If you could change the layout such that each category is in its own table:

Data Range

	A	B	C	D
1	Coach	February	March	April
2		Steve	Steve	Joe
3		Tom	Tom	Jess
4		Steph	Steph	Tim
5		Joe	Emily	Rita
6		Jess	Jess	Matt
7		Tim	Tim	Khan
8		Rob	Rita	Chris
9		Bex	Kylee	Steve
10		Rachel	Avril	Tom
11		Pam	Rex	Steph
12
13	Celebrate	Matt	Matt	Kylee
14		Khan	Khan	Avril
15		Chris	Chris	Rex
16		Emily	Joe	Emily
17		Jeff	Jeff	Jeff
18		Vita	Vita	Vita
19		Rita	Rob	Rob
20		Kylee	Bex	Bex
21		Avril	Rachel	Rachel
22		Rex	Pam	Pam

Then the formula could be made more dynamic.

Data Range

	G	H	I	J
1	Coach		Celebrate
2	Joe	1	Kylee	1
3	Jess	3	Avril	1
4	Tim	3	Rex	1
5	Rita	2	Emily	1
6	Matt	1	Jeff	3
7	Khan	1	Vita	3
8	Chris	1	Rob	2
9	Steve	3	Bex	2
10	Tom	3	Rachel	2
11	Steph	3	Pam	2
12		------		------

This array formula** entered in H2 and copied down:

=MAX(FREQUENCY(IF(COUNTIF(OFFSET(B$2:B$11,,COLUMN(B$2:D$11)-COLUMN(B$2)),G2)=1,COLUMN(A:C)),IF(COUNTIF(OFFSET(B$2:B$11,,COLUMN(B$2:D$11)-COLUMN(B$2)),G2)<>1,COLUMN(A:C))))

This array formula** entered in J2 and copied down:

=MAX(FREQUENCY(IF(COUNTIF(OFFSET(B$13:B$22,,COLUMN(B$13:D$22)-COLUMN(B$13)),I2)=1,COLUMN(A:C)),IF(COUNTIF(OFFSET(B$13:B$22,,COLUMN(B$13:D$22)-COLUMN(B$13)),I2)<>1,COLUMN(A:C))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

M3=

Please Login or Register  to view this content.

Try this array (Shift+Ctrl+Enter) formula and copy towards down