Getting Weekly Numbers from a range where dates are in a single row

[justme101]

Hello Experts,

I have a excel sheet where names of the participants of a daily quiz are saved, along with the answers they submitted and if they did not participate that day, the corresponding cell for that day is marked as "No Answer". A sample is given below:

Team Members	05-04-2021	06-04-2021	07-04-2021	08-04-2021	12-04-2021	15-04-2021	16-04-2021	19-04-2021
John Wick	A, B & C	C) 1, 2, 3 & 4	D) 1, 2, 3 & 4	No Answer	No Answer	B) 2 Only;	True	A & B
Agent 42	No Answer	No Answer	No Answer	False	No Answer	C) 1 & 2;	No Answer	A & B
Ezio Auditore	A, B & C	D)1 & 4	No Answer	True	True	No Answer	No Answer	A & B
WeekNumber>	15	15	15	15	16	16	16	17

What I need, is to show a chart which shows "participation %" for 1) All Team Members, and 2) Individual Team Members, on a weekly basis. I am confused as to how to even start. Any help is appreciated. Thank you, in anticipation.

 

[Saba Sabaratnam]

Hi

Create a data summary table as shown below.

Enter the following formula in C12 and copy it down to C14 and across to E14

=SUMPRODUCT(--(($C$3:$J$5<>"No Answer")*($C$7:$J$7=C$11)*($B$3:$B$5=$B12)))

in Cell c15 and copy it across E15

=SUM(C12:C14)

in Cell C17 to E17

=SUMPRODUCT(--(($C$3:$J$5<>"")*($C$7:$J$7=C11)))

Then calculate the % and create a graph you want. A sample chart is shown below.

Kind regards

Saba