Help w/ Excel Formula

[nat4bes]

I have a spreadsheet to track member attendance for each monthly meeting. Column A (A2:A23) contains a list of member names. In columns C thru N are the dates of 12 meetings of this year. Some members may have a status of either "in person", "Teams", or "canceled" for the past 9 months. If the member does not attend a meeting for the corresponding month, the cell is blank. If the meeting is canceled all the members have "canceled" in that column for the month. I need a formula in column O that will calculate the percentage each individual member has attended meetings for the year based on when/what month I open the file.

My current formula is =(COUNTIF(C2:N2, "in person") + COUNTIF(C2:N2, "Teams") + COUNTIF(C2:N2, "Canceled"))/12 but the member who has attended 0 meetings looks like he is at 8% meeting attendance because 1 meeting in the past 9 months was canceled. I also tried =SUM(COUNTIF(C2:N2,{"in person","teams"}))-COUNTIF(C2:N2,"Canceled") but that give that same member is at a negative number instead of 0%.

I would appreciate any help you can give me. Thanks!

Name

Notes

01-15-2024

02-12-2024

03-18-2024

04-22-2024

05-20-2024

06-24-2024

07-08-2024

08-19-2024

09-23-2024

10-14-2024

11-11-2024

12-09-2024

% of 2024 attendance

John Doe

in person

in person

Teams

Canceled

in person

Bart Simpson

in person

in person

Teams

Canceled

in person

in person

in person

in person

Monica Geller

Teams

Canceled

in person

Frazier Crane

in person

in person

Teams

Canceled

in person

in person

in person

in person

in person

Murphy Brown

Canceled

Jerry Seinfeld

in person

in person

Canceled

in person

in person

 

[RobP]

Just to ask, if you had the meeting on 10-14-2024, and nobody showed up. How would you recognise that ?

Rob

 

[JGordon11]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Notes	1/15/2024	2/12/2024	3/18/2024	4/22/2024	5/20/2024	6/24/2024	7/8/2024	8/19/2024	9/23/2024	10/14/2024	11/11/2024	12/9/2024	% of 2024 attendance
2	John Doe		in person	in person	Teams	Canceled			in person						50%
3	Bart Simpson		in person	in person	Teams	Canceled	in person	in person		in person	in person				88%
4	Monica Geller				Teams	Canceled		in person							25%
5	Frazier Crane		in person	in person	Teams	Canceled	in person	in person	in person	in person	in person				100%
6	Murphy Brown					Canceled									0%
7	Jerry Seinfeld		in person	in person		Canceled		in person		in person					50%
Sheet2

Cell Formulas
Range	Formula
O2:O7	O2	=LET(a,C2:N2, SUM((a="in person")+(a="Teams"))/(SUM(--($C$1:$N$1<=TODAY())) - SUM(--(a="Canceled"))))

 

[nat4bes]

That worked! Thank you JGordon11

 

[JGordon11]

You're welcome. I would recommend data validation for the data range (in the example C2:N7), because any typo in spelling "in person", "Teams" or "Canceled" will cause the formula to provide unintended results. For example if you had "in person" with two spaces between "in" and "person", that person would not receive credit for attending that month. You could add a TRIM function to take care of potential additional spaces, but that will only correct for one kind of typo. Data validation will make it less likely to introduce an error (although pasting into a data validated cell will bypass the validation).