What is the best way to count a set of data

Question

Hello guys!&nbsp;I encounter the following problem. I need to count number of occurrence of OFF, MC, AL and etc day for James, Jane and John for the set of date range. (as per sample data)&nbsp;DateJamesJaneJohn21-Aug-18PPP22-Aug-18OFFPP23-Aug-18PPP24-Aug-18PPP25-Aug-18PPP26-Aug-18OFFOFFP27-Aug-18PPP28-Aug-18PPP29-Aug-18PHDP30-Aug-18PPP31-Aug-18OFFOFFP1-Sep-18PMCP2-Sep-18OFFOFFP3-Sep-18PMCP4-Sep-18PPP5-Sep-18PPP6-Sep-18PPP7-Sep-18PPP8-Sep-18PPP9-Sep-18OFFOFFP10-Sep-18OFFOFFP11-Sep-18PLATEP12-Sep-18PPP13-Sep-18PLATEP14-Sep-18PPP15-Sep-18PPP16-Sep-18POFFP17-Sep-18POFFP18-Sep-18PPAL19-Sep-18PMCP20-Sep-18&nbsp;PP&nbsp;&nbsp;Need to summarize it as follows&nbsp;&nbsp;&nbsp;JamesJaneJohnOFF670UPL&nbsp;&nbsp;&nbsp;AL&nbsp;&nbsp;1&nbsp;HD&nbsp;&nbsp;1&nbsp;MC&nbsp;3&nbsp;&nbsp;LATE&nbsp;2&nbsp;&nbsp;ABS&nbsp;&nbsp;&nbsp;&nbsp;What is the best way to do it? Appreciate your help!&nbsp;&nbsp;

sergeibaklan · Answer

Hi Leow,
&nbsp;
For data as here

that could be
=COUNTIF(OFFSET($A$1,1,MATCH(G$1,$B$1:$D$1,0),COUNT($A$2:$A$1000)),$F2)
in G2, drag it down and to the right.
Attached
&nbsp;

leow chee voon · Answer

Dear Sergei,Thanks for your tips. It works wonderfully!!

sergeibaklan · Answer

Leow, your are welcome

Forum Discussion

What is the best way to count a set of data

Share

Resources

Date	James	Jane	John
21-Aug-18	P	P	P
22-Aug-18	OFF	P	P
23-Aug-18	P	P	P
24-Aug-18	P	P	P
25-Aug-18	P	P	P
26-Aug-18	OFF	OFF	P
27-Aug-18	P	P	P
28-Aug-18	P	P	P
29-Aug-18	P	HD	P
30-Aug-18	P	P	P
31-Aug-18	OFF	OFF	P
1-Sep-18	P	MC	P
2-Sep-18	OFF	OFF	P
3-Sep-18	P	MC	P
4-Sep-18	P	P	P
5-Sep-18	P	P	P
6-Sep-18	P	P	P
7-Sep-18	P	P	P
8-Sep-18	P	P	P
9-Sep-18	OFF	OFF	P
10-Sep-18	OFF	OFF	P
11-Sep-18	P	LATE	P
12-Sep-18	P	P	P
13-Sep-18	P	LATE	P
14-Sep-18	P	P	P
15-Sep-18	P	P	P
16-Sep-18	P	OFF	P
17-Sep-18	P	OFF	P
18-Sep-18	P	P	AL
19-Sep-18	P	MC	P
20-Sep-18	P	P