I wrote:
"reznor9" wrote"
=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."
[....]
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),...)

For the latter interpretation, array-enter the following formula (press
ctrl+shift+Enter instead of just Enter):

=SUM(IF(J.Adams!$F$4:$F$24=TRANSPOSE(Summary!$AA$1 4:$AA$23),J.Adams!$E$4:$E$24))

When array-entered, you should curly braces around the entire formula (i.e.
{=formula}) when it is displayed in the Formula Bar. You cannot type the
curly braces yourself; that is just Excel's way of identifying the formula
as array-entered.