"reznor9" wrote"
If I insert this formula it works fine.
=SUM(IF(J.Adams!$F$4:$F$24=(Summary!$AA$14),(J.Ada ms!$E$4:$E$24)))

The forumla above only adds up one of 10 possible overtime values. now
when I try to extend the parameters of the equasion to include all 10
dropdown options as I have below it doesnt work.

=SUM(IF(J.Adams!$F$4:$F$24=(Summary!$AA$14:$AA$23) ,(J.Adams!$E$4:$E$24)))
says "value is not available to the formula or function."

No it doesn't, at least not in a US-English version of Excel.

The latter formula returns a #N/A, which __means__ "value is not available
to the formula or function".

First, I hope you are array-entering those formulas; that is, pressing
ctrl+shift+Enter instead of just Enter. Otherwise, the formulas might
return a value in some (not all) contexts, but it is not the intended value
(I presume).

Second, the #N/A is because the not all the ranges are the same size (and
shape). F4:F24 and E4:E24 comprise 21 cells, whereas AA14:AA23 comprises 10
cells.

Also, your expression is ambiguous with respect to what you might mean by
it. If you correct the syntax error (I don't know how!), it will be
interpreted effectively as (simplifying for brevity):

=SUM(IF(F4=AA14,E4),IF(F5=AA15,E5),IF(F6=AA16,E6), ...)

Is that really the interpretation you intended? Or did you intend something
like:

=SUM(IF(OR(F4=AA14,F4=AA15,...,F4=AA23),E4),
IF(OR(F5=AA14,F5=AA15,....,F5=AA23),E5),...)