Countif and Countifs

Some of your values in column U have "trailing spaces" - a "C" followed by a space, so that isn't counted. Get rid of those and the formula should count OK.......or you can switch to this version

=SUMPRODUCT(($C$2:$Z$2=AA$2)*(TRIM($C3:$Z3)={"C";"D";"P";"N"}))

which uses TRIM in the formula to get rid of any additional spaces - so that should give you the correct count without changing the data