Formula to calculate team attendance %

mattbird · Oct 20, 2023

Hi,

I have been building an attendance log, which calculates an individuals attendance, however I would like it to also calculate the teams attendance in column 'W'.

I have placed all the formulas in their cells (Row 11 only), as well as the results for all individuals etc.

I initial had a sum that added the individual % together, however this does not always work especially is there are multiple team members attending on the same day etc, as you can see BRAVO team would be 217%, but should be 100%, as a member of the team has attend at each of the 6 meetings.

I hope this make sense?

Your help is greatly appreciated

	A	B	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
	Team	Name	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Attendance '1'	Deputy 'D'	Apologies 'A'	DNA '0'	Not Required 'N'	Fx Total (P:S)	Individual Attendance %	Team Attendance %
11	ALPHA	Jim	A	1	1	1	1	1							5 Excel Formula: `=COUNTIF($D11:$O11,1)`	0 Excel Formula: `=COUNTIF($D11:$O11,"D")`	1 Excel Formula: `=COUNTIF($D11:$O11,"A")`	0 Excel Formula: `=COUNTIF($D11:$O11,"0")`	0 Excel Formula: `=COUNTIF($D11:$O11,"N")`	6 Excel Formula: `=SUM(P11:S11)`	83% Excel Formula: `=IFERROR(IF(COUNTIFS($D11:$O11,"")=12,0,SUM(COUNTIFS($D11:$O11,{1,"D"}))/$U11),0)`	100%
12	ALPHA	Clare	1	A	A	1	1	A							3	0	3	0	0	6	50%
13	BRAVO	Si	N	1	1	N	1	1							4	0	0	0	2	4	100%	100%
14	BRAVO	John	0	D	A	1	1	1							3	1	1	1	0	6	67%
15	BRAVO	Liz	1	N	N	0	1	A							2	0	1	1	2	4	50%
16	CHARLIE	Jess	0	0	0	0	0	1							1	0	0	5	0	6	17%	67%
17	CHARLIE	Paul	1	1	1	A	A	0							3	0	2	1	0	6	50%

ExceLoki · Oct 20, 2023

try this
-----------------------

Book1

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

10

Team

Name

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Attendance

Deputy

Apologies

DNA

Not Required

Fx Total

Individual

Team Attendance %

11

1

D

A

0

N

(P:S)

Attendance %

12

13

ALPHA

Jim

A

1

14

ALPHA

Clare

1

A

1

A

15

BRAVO

Si

N

1

N

1

16

BRAVO

John

0

D

A

1

17

BRAVO

Liz

1

N

0

1

A

18

CHARLIE

Jess

0

1

19

CHARLIE

Paul

1

A

0

Sheet1

Cell Formulas
Range	Formula
P13:T19	P13	=COUNTIF($D13:$O13,P$11)
U13:U19	U13	=SUM(P13:S13)
V13:V19	V13	=IFERROR(IF(COUNTIFS($D13:$O13,"")=12,0,SUM(P13,Q13)/$U13),0)
W13:W19	W13	=IFERROR(IF(COUNTIFS($D13:$O13,"")=12,0,SUM(SUMIF($B$13:$B$19,$B13,P$13:P$19),SUMIF($B$13:$B$19,$B13,Q$13:Q$19))/SUMIF($B$13:$B$19,$B13,$U$13:$U$19)),0)

mattbird · Oct 20, 2023

ExceLoki said:
try this
-----------------------

Book1
B C D E F G H I J K L M N O P Q R S T U V W
10 Team Name Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Attendance Deputy Apologies DNA Not Required Fx Total Individual Team Attendance %
11 1 D A 0 N (P:S) Attendance %
12
13 ALPHA Jim A 1 1 1 1 1 5 0 1 0 0 6 83% 67%
14 ALPHA Clare 1 A A 1 1 A 3 0 3 0 0 6 50% 67%
15 BRAVO Si N 1 1 N 1 1 4 0 0 0 2 4 100% 71%
16 BRAVO John 0 D A 1 1 1 3 1 1 1 0 6 67% 71%
17 BRAVO Liz 1 N N 0 1 A 2 0 1 1 2 4 50% 71%
18 CHARLIE Jess 0 0 0 0 0 1 1 0 0 5 0 6 17% 33%
19 CHARLIE Paul 1 1 1 A A 0 3 0 2 1 0 6 50% 33%
Sheet1
Cell Formulas
Range Formula
P13:T19 P13 =COUNTIF($D13:$O13,P$11)
U13:U19 U13 =SUM(P13:S13)
V13:V19 V13 =IFERROR(IF(COUNTIFS($D13:$O13,"")=12,0,SUM(P13,Q13)/$U13),0)
W13:W19 W13 =IFERROR(IF(COUNTIFS($D13:$O13,"")=12,0,SUM(SUMIF($B$13:$B$19,$B13,P$13:P$19),SUMIF($B$13:$B$19,$B13,Q$13:Q$19))/SUMIF($B$13:$B$19,$B13,$U$13:$U$19)),0)

Hi,

Thanks for your help.

I don't think that the % in row 'W' are correct:

As ALPHA and BRAVO teams should be 100% attendance and CHARLIE team should be 67% attendance?

Key:
1 = attended
0 = did not attend
A = apologies
D = deputy
N = not required

for example: BRAVO team, individually they have not attended all of the meeting, however together there has been a member of BRAVO team at each meeting so the would be 100% attendance not 71%


A	B	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
	Team	Name	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Attendance '1'	Deputy 'D'	Apologies 'A'	DNA '0'	Not Required 'N'	Fx Total (P:S)	Individual Attendance %	Team Attendance %
11	ALPHA	Jim	A	1	1	1	1	1							5 Excel Formula: =COUNTIF($D11:$O11,1)	0 Excel Formula: =COUNTIF($D11:$O11,"D")	1 Excel Formula: =COUNTIF($D11:$O11,"A")	0 Excel Formula: =COUNTIF($D11:$O11,"0")	0 Excel Formula: =COUNTIF($D11:$O11,"N")	6 Excel Formula: =SUM(P11:S11)	83% Excel Formula: =IFERROR(IF(COUNTIFS($D11:$O11,"")=12,0,SUM(COUNTIFS($D11:$O11,{1,"D"}))/$U11),0)	100%
12	ALPHA	Clare	1	A	A	1	1	A							3	0	3	0	0	6	50%
13	BRAVO	Si	N	1	1	N	1	1							4	0	0	0	2	4	100%	100%
14	BRAVO	John	0	D	A	1	1	1							3	1	1	1	0	6	67%
15	BRAVO	Liz	1	N	N	0	1	A							2	0	1	1	2	4	50%
16	CHARLIE	Jess	0	0	0	0	0	1							1	0	0	5	0	6	17%	67%
17	CHARLIE	Paul	1	1	1	A	A	0							3	0	2	1	0	6	50%

As you can see the RED 1's (highlighted for example only) show a person at each meeting from Apr-Sep and the BLUE 1's (example only) shown were other team member attending the same meeting. Overall there has only been 6 meeting so far and BRAVO team has attended each meeting, just a different person, so it should read 100% attendance in row 'W'.

Does this make sense?

Fluff · Oct 20, 2023

How about

Fluff.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

10

Team

Name

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Attendance '1'

Deputy 'D'

Apologies 'A'

DNA '0'

Not Required 'N'

Fx Total (P:S)

Individual Attendance %

Team Attendance %

11

ALPHA

Jim

A

1

12

ALPHA

Clare

1

A

1

A

3

0

3

0

6

50%

100%

13

BRAVO

Si

N

1

N

1

4

0

2

4

100%

14

BRAVO

John

0

D

A

1

3

1

0

6

67%

100%

15

BRAVO

Liz

1

N

0

1

A

2

0

1

2

4

50%

100%

16

CHARLIE

Jess

0

1

0

5

0

6

17%

67%

17

CHARLIE

Paul

1

A

0

3

0

2

1

0

6

50%

67%

Data

Cell Formulas
Range	Formula
P11	P11	=COUNTIF($D11:$O11,1)
Q11	Q11	=COUNTIF($D11:$O11,"D")
R11	R11	=COUNTIF($D11:$O11,"A")
S11	S11	=COUNTIF($D11:$O11,"0")
T11	T11	=COUNTIF($D11:$O11,"N")
U11	U11	=SUM(P11:S11)
V11:V17	V11	=SUM(P11:Q11)/U11
W11:W17	W11	=SUM(SIGN(MMULT(TRANSPOSE(ROW($B$11:$B$17)^0),($B$11:$B$17=B11)*(($D$11:$O$17=1)+($D$11:$O$17="D")))))/SUM(P11:T11)

mattbird · Oct 23, 2023

Fluff said:
How about

Fluff.xlsm
A B C D E F G H I J K L M N O P Q R S T U V W
10 Team Name Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Attendance '1' Deputy 'D' Apologies 'A' DNA '0' Not Required 'N' Fx Total (P:S) Individual Attendance % Team Attendance %
11 11 ALPHA Jim A 1 1 1 1 1 5 0 1 0 0 6 83% 100%
12 12 ALPHA Clare 1 A A 1 1 A 3 0 3 0 0 6 50% 100%
13 13 BRAVO Si N 1 1 N 1 1 4 0 0 0 2 4 100% 100%
14 14 BRAVO John 0 D A 1 1 1 3 1 1 1 0 6 67% 100%
15 15 BRAVO Liz 1 N N 0 1 A 2 0 1 1 2 4 50% 100%
16 16 CHARLIE Jess 0 0 0 0 0 1 1 0 0 5 0 6 17% 67%
17 17 CHARLIE Paul 1 1 1 A A 0 3 0 2 1 0 6 50% 67%
Data
Cell Formulas
Range Formula
P11 P11 =COUNTIF($D11:$O11,1)
Q11 Q11 =COUNTIF($D11:$O11,"D")
R11 R11 =COUNTIF($D11:$O11,"A")
S11 S11 =COUNTIF($D11:$O11,"0")
T11 T11 =COUNTIF($D11:$O11,"N")
U11 U11 =SUM(P11:S11)
V11:V17 V11 =SUM(P11:Q11)/U11
W11:W17 W11 =SUM(SIGN(MMULT(TRANSPOSE(ROW($B$11:$B$17)^0),($B$11:$B$17=B11)*(($D$11:$O$17=1)+($D$11:$O$17="D")))))/SUM(P11:T11)

Hi Fluff,

Thanks for your response and help.

I have tried the formula in cell 'W' however it returns #VALUE!?

Fluff · Oct 23, 2023

You may need to confirm it with Ctrl Shift Enter, rather than just Enter.

mattbird · Oct 26, 2023

Fluff said:
You may need to confirm it with Ctrl Shift Enter, rather than just Enter.

Hi Fluff,

I have 2 questions, if that ok?

Q1 - I've notice if I filling in a column and leave a cell black it affects the percentage for that row, how do I stop this, so blank cells don't affect the result?

which brings me on to my next question.

Q2 - The current formula works for each individual row, and produces a result for each row in column 'W'. How would I get one result for each team in one cell?

Example: If I merged cell W11:W12 for Alpha, W13:W15 for Bravo, W16:W17 for Charlie or a different cell all together ie table below:

	A	B
20	Alpha team	100%
21	Bravo team	100%
22	Charlie team	67%

Thanks for your continued help.

Fluff · Oct 26, 2023

For part 2 use

Fluff.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

10

Team

Name

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Attendance '1'

Deputy 'D'

Apologies 'A'

DNA '0'

Not Required 'N'

Fx Total (P:S)

Individual Attendance %

Team Attendance %

11

ALPHA

Jim

A

1

12

ALPHA

Clare

1

A

1

A

3

0

3

0

6

0.5

13

BRAVO

Si

N

1

N

1

4

0

2

4

1

100%

14

BRAVO

John

0

D

A

1

3

1

0

6

0.66666667

15

BRAVO

Liz

1

N

0

1

A

2

0

1

2

4

0.5

16

CHARLIE

Jess

0

1

0

5

0

6

0.16666667

67%

17

CHARLIE

Paul

1

A

0

3

0

2

1

0

6

0.5

Data

Cell Formulas
Range	Formula
P11	P11	=COUNTIF($D11:$O11,1)
Q11	Q11	=COUNTIF($D11:$O11,"D")
R11	R11	=COUNTIF($D11:$O11,"A")
S11	S11	=COUNTIF($D11:$O11,"0")
T11	T11	=COUNTIF($D11:$O11,"N")
U11	U11	=SUM(P11:S11)
V11:V17	V11	=SUM(P11:Q11)/U11
W11:W17	W11	=IF(COUNTIFS(B$11:B11,B11)=1,SUM(SIGN(MMULT(TRANSPOSE(ROW($B$11:$B$17)^0),($B$11:$B$17=B11)*(($D$11:$O$17=1)+($D$11:$O$17="D")))))/SUM(P11:T11),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

For part 1: How can you leave a cell blank, surely the person turned up or didn't.

Formula to calculate team attendance %

mattbird

Active Member

Excel Facts

ExceLoki

Well-known Member

mattbird

Active Member

Fluff

MrExcel MVP, Moderator

mattbird

Active Member

Fluff

MrExcel MVP, Moderator

mattbird

Active Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Formula to calculate team attendance %

Active Member

Excel Facts

Well-known Member

Active Member

MrExcel MVP, Moderator

Active Member

MrExcel MVP, Moderator

Active Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock