automatic distribution

[abdulla88]

I have this main table (no1.) Is there any way to automatically distribute the required values in Table 2 and 3?

test.xlsx
	A	B	C	D
1	Table 1
2	Patient	ward	Nationality	Action
3	ABC	101	USA	discharge
4	DEF	201	UK	Sick
5	GHI	101	UK	Fit for Discharge
6	JKL	201	USA	discharge
7	MNO	101	USA	Sick
8
9
10
11
12	Table 2
13	ACTION	USA	UK
14	discharge	2	0
15	Fit for Discharge	0	1
16	Sick	1	1
17
18
19
20
21	Table 3
22	WARD	TOTAL
23	101	3
24	201	2
Sheet1

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D
1	Table 1
2	Patient	ward	Nationality	Action
3	ABC	101	USA	discharge
4	DEF	201	UK	Sick
5	GHI	101	UK	Fit for Discharge
6	JKL	201	USA	discharge
7	MNO	101	USA	Sick
8
9
10
11
12	Table 2
13	ACTION	USA	UK
14	discharge	2	0
15	Fit for Discharge	0	1
16	Sick	1	1
17
18
19
20
21	Table 3
22	WARD	TOTAL
23	101	3
24	201	2
25
Main

Cell Formulas
Range	Formula
B14:C16	B14	=COUNTIFS($C$3:$C$7,B$13,$D$3:$D$7,$A14)
B23:B24	B23	=COUNTIFS($B$3:$B$7,A23)