Count of Names in Vertical List(according to criteria), then arrange Horizontally...

Hi,

If the whole table is a list of absences then aren't the column I totals the same as column F?

It would help if you were to add manually a few examples of the results you expect. However I can't help thinking this can't be achieved in more elegant,efficient and useful way with a Pivot Table. If you don't have Excel 2013 as your profile suggests then you may need to add a helper column to your data but that's a very small price to pay for a much more powerful analysis tool.

I would suggest the best and most efficient approach is a Pivot Table. It won't be exactly the layout you have specified but it would give the information you require.

	F	G	H	I	J	K	L	M	N
13
14	Count of REASON	Column Labels
15	Row Labels	A/L	Appointment	Compassionate Leave	Illness	Meeting	Personal	Training	Grand Total
16	ACCOUNTS
17	A JONES		1			2			3
18	D HACKER	2				1			3
19	J PORTEOUS	1		2		2			5
20	ACCOUNTS Total	3	1	2		5			11
21	Administration
22	R RAINEY	1		1	1				3
23	S CORDER	1				2			3
24	Administration Total	2		1	1	2			6
25	CLEAN
26	O LEMB				1	2			3
27	T MCGRADY		1	1	2				4
28	CLEAN Total		1	1	3	2			7
29	HELPD
30	B FENNELL			1		2			3
31	D HACKER							1	1
32	HELPD Total			1		2		1	4
33	INDOOR
34	A JONES	1			2				3
35	K BRYAN	1		1			1		3
36	INDOOR Total	2		1	2		1		6
37	IT
38	D HACKER			1				1	2
39	E SMITH		1					1	2
40	M MARTHER				2				2
41	IT Total		1	1	2			2	6
42	SLT
43	F HOPE	2			3				5
44	G S STRAVER	1		1		1			3
45	SLT Total	3		1	3	1			8
46	Grand Total	10	3	8	11	12	1	3	48

Hi,

With a helper column E

Formula:

Please Login or Register  to view this content.

Then in J2 (formerly I before the new helper column) copied down

Formula:

Please Login or Register  to view this content.

When it comes to the names then there's probably some complex compound formula that can identify the names but as both Trevor and I have remarked it does seem that a Pivot Table is perhaps the best way forward.