Using Countifs/Match/Index formula to count data in different columns

I am trying to count data that spans multiple columns, but only where it matches someone's name.

For instance, I have the below sample table.

	Week1	Week2	Week3
Name3	On Time	On Time	On Time
Name1	On Time	Not Submitted	On Time
Name2	Not Submitted	Not Submitted

This is my base data. I'm trying to count how many times someone has submitted their work "On Time." But I want to be able to search for the name1-3, not have them in specific rows. So I have a seperate table below that I am using to count the data with.

	On Time
Name1
Name2
Name3

I have also attached my example. I have a formula in column B2 to count this data, but it is not working properly. Can anyone help me with this? Am I using the right kind of formula, or do I have the wrong idea complete?

Please Login or Register  to view this content.

Book1.xlsx

Thanks!

Thank you so much. I have been going crazy wondering why this wouldn't work. Such a simple mistake. Thanks!!!

Guys...can anyone help me....i am also trying to calculate the person how many times present so that i can make graph on this.
i am using formula like this,
=COUNTIF(INDEX(E2:L11,MATCH(A2,E2:E11,0)),"P")

Name P Count Days Absent Name 1 2 3 4 5 6 7
Anup #REF! 7 #REF! Rajesh P P A P P A A
Amar #REF! 7 #REF! Anup A A P P A P A
Rajesh #N/A 7 #N/A Soham P A P P P P P
Vinod #REF! 7 #REF! Vinod P P P P A P A
Sachin #REF! 7 #REF! Vinay A P P A A P A
Soham #N/A 7 #N/A Amar P P P P P A P
Manoj #REF! 7 #REF! Santosh A A P P A P A
Santosh #N/A 7 #N/A Sachin A P P A P A P
Kuldeep #REF! 7 #REF! Kuldeep P P P P P P A
Vinay #N/A 7 #N/A Manoj P P P P A P P

What if I want to solve an extension to this problem. Let's say Name1 appears more than once in column A and I want to see the count of all 'On Time' for Name1 for Week1? Please suggest.

I have the same question. Can anyone help please?

Say thanks, Click *