How to count values on selected cells in excel(not by VBA)

[m_vishal_c]

HI
I do have values like below

Aged Care Name	Dept	Staff Name	Designation	Flu	Covid 1	Covid 2
B	abc	John	RN	12/02/2021​	16/08/2021​
A	xyz	Smith	AIN	1/01/2021​	6/02/2021​	6/08/2021​
B	aaaa	Camelia	EN
B	ggg	Issac	AIN	4/07/2021​	5/08/2021​

like in Aged care B, according to total Staff members how many % Flu , Covid 1 % and Covid 2 % completed.

so for Aged care B there are total 3 Staffs and only 2 staff got shot of Flu so 2/3=66.67% Flu vaccination completed

There are many Aged care like A to Z. I need to count for all Aged care how many % Flu, % Covid 1, % Covid 2 completed

I just need the formula (not VBA) for Flu, Covid 1 and Covid 2

heaps thanks in Advance.

 

[m_vishal_c]

Thanks for quick reply. What will be the formula for K2 and L2. Do i need to add Aged care names (A,B,C....) manually in column I. Thanks

 

[Marcelo Branco]

*Deleted previous post*

Try
Formula in J2 copied across and down

Pasta1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Aged Care Name	Dept	Staff Name	Designation	Flu	Covid 1	Covid 2		Aged Care	Flu	Covid 1	Covid 2
2	B	abc	John	RN	12/02/2021	16/08/2021			A	100%	100%	100%
3	A	xyz	Smith	AIN	01/01/2021	06/02/2021	06/08/2021		B	67%	67%	0%
4	B	aaaa	Camelia	EN
5	B	ggg	Issac	AIN	04/07/2021	05/08/2021
6
Plan2

Cell Formulas
Range	Formula
J2:L3	J2	=COUNTIFS($A$2:$A$5,$I2,E$2:E$5,"<>")/COUNTIF($A$2:$A$5,$I2)

M.

 

[m_vishal_c]

*Deleted previous post*

Try
Formula in J2 copied across and down

Pasta1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Aged Care Name	Dept	Staff Name	Designation	Flu	Covid 1	Covid 2		Aged Care	Flu	Covid 1	Covid 2
2	B	abc	John	RN	12/02/2021	16/08/2021			A	100%	100%	100%
3	A	xyz	Smith	AIN	01/01/2021	06/02/2021	06/08/2021		B	67%	67%	0%
4	B	aaaa	Camelia	EN
5	B	ggg	Issac	AIN	04/07/2021	05/08/2021
6
Plan2

Cell Formulas
Range	Formula
J2:L3	J2	=COUNTIFS($A$2:$A$5,$I2,E$2:E$5,"<>")/COUNTIF($A$2:$A$5,$I2)

M.

And what about Aged care names in column I. Do i have to enter manually?

 

[Marcelo Branco]

And what about Aged care names in column I. Do i have to enter manually?

Yes, they are the criteria to be used in the formulas.
Paste the Aged Care Names in column I and go to
Data > Remove duplicates
to create the list

M.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, if you have Excel 365 you may be able to use this single formula I2?

21 08 17.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Aged Care Name	Dept	Staff Name	Designation	Flu	Covid 1	Covid 2		Aged Care	Flu	Covid 1	Covid 2
2	B	abc	John	RN	12/02/2021	16/08/2021			A	100%	100%	100%
3	A	xyz	Smith	AIN	1/01/2021	6/02/2021	6/08/2021		B	67%	67%	0%
4	B	aaaa	Camelia	EN
5	B	ggg	Issac	AIN	4/07/2021	5/08/2021
Calc %

Cell Formulas
Range	Formula
I2:L3	I2	=LET(r,A2:A5,u,UNIQUE(r),ca,COUNTIF(r,u),SORT(CHOOSE({1,2,3,4},u,COUNTIFS(r,u,E2:E5,">0")/ca,COUNTIFS(r,u,F2:F5,">0")/ca,COUNTIFS(r,u,G2:G5,">0")/ca)))
Dynamic array formulas.

 

[m_vishal_c]

HI Marcelo Branco, Peter_SSs
I really appreciate your support and it s perfect solution for my query. Thanks a lot

 

[Marcelo Branco]

You're welcome. Glad we could help.

M.