Attendance Register how to count....

[offensive16]

Hi there,
Like the above recently I've created an attendance register. I have names in rows and the dates in columns. If someone is either present, absent or on holiday in the cells relatively I type values P, A or H.

Below the main table with my data I created a data validation list from which I want to choose a particular name and I want excel to count for me how many days that person was either present, absent or on holiday.

I've tried many ways already using countifs, countif, and if functions and acutally it worked but it doesn't happen dynamically. If I change the name of a person I want the values to change as well.



Thank you in advance for help.

 

[Momentman]

As it says "Present" in D14 think better with
=SUMPRODUCT(($A$3:$A$8=$C16)*($B$3:$I$8=LEFT(D$14,1)))

I think C16 should be C15, and A3:A8 should be A2:A7
=SUMPRODUCT(($A$2:$A$7=$C15)*($B$2:$I$7=LEFT(D$14,1)))

 

[gaz_chops]

I think C16 should be C15, and A3:A8 should be A2:A7
=SUMPRODUCT(($A$2:$A$7=$C15)*($B$2:$I$7=LEFT(D$14,1)))

Thanks, A2 should be A3
=SUMPRODUCT(($A$3:$A$8=$C15)*($B$3:$I$8=LEFT(D$14,1)))

Think were both having problems copying and pasting

 

[offensive16]

Thank you guys for your help, it's unbelievable what excel is capable of.. I've spent over 3 hours on this and it takes only a minute to do ...

 

[gaz_chops]

You're welcome, joint effort by Momentman & myself got there in the end!