Formula to search specific cells for number then add data from other rows

I HAVE A RANGE FROM (C19-K27) DEPENDING ON THE SERIAL NUMBERS IN THAT RANGE I KEEP TRACK OF DATA FROM ROWS 14 & 16. WHAT I WOULD LIKE IS FOR EXAMPLE IN THE BOX IN COLUMN "O" DEPENDING WHAT I HAVE SELECTED I WANT THE FORMULA TO SEARCH THE RANGE (C19-K27) AND IF IT FINDS THAT EXACT SERIAL NUMBER TO ADD THE DATA IN ROW 16 AND HAVE THAT IN (P9) THEN USE THE DATA IN ROW 14 THAT ANSWER GOES INTO (Q9) WHICH CORRESPONDS FOR THAT SERIAL NUMBER.

i HAVE ATTACHED A SAMPLE SHEET

THANKS FOR YOUR HELP

I think your caps lock is stuck!

Try this one in P9

=SUMPRODUCT(($C$19:$K$28=T(O9))*$C$16:$K$16)

Yes that worked in P9 but I changed it for Q9 to this

=SUMPRODUCT(($C$19:$K$28=T(O9))*$C$16:$K$16)

all I get is #Value

Sorry about caps lock that's all we use over here i forget to turn it off sometimes.

For Q9, it should be

=SUMPRODUCT(($C$19:$K$28=T(O9))*$C$14:$K$14)

Not sure what you tried, the formula in your reply was the same as the original one?

You are correct my mistake i used
=SUMPRODUCT(($C$19:$K$28=T(O9))*$C$14:$K$14)

the same is you but I get #Value as an answer

I just noticed that you have formula blanks in F14 and H14:K14 which are causing the error.

Try this insted, which will need to be array confirmed with Shift Ctrl Enter.

=SUMPRODUCT(($C$19:$K$28=T(O9))*IF(ISNUMBER($C$14:$K$14),$C$14:$K$14))

sorry to be a pain butt hat returned 0.00

No curly brackets in the formula bar?

If you got 0.00 then the array is not confirmed.

Double click in the cell, then press Shift Ctrl and Enter simultaneously.

That worked thank you

I noticed that as you carry the formula down and if range O(9-15) is blank it adds numbers that should not be added

I noticed that earlier, could have sworn that using the T() function had fixed that.

=IF(O9="","",SUMPRODUCT(($C$19:$K$28=O9)*IF(ISNUMBER($C$14:$K$14),$C$14:$K$14)))

Array confirmed in Q9

Given the odd problems appearing here and there, it might be benificial to use this formula, with the rows changed in P9 as well.