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.

Been a while since I worked on this and I still have not figured it out.
Yes I am array entering the formula.
Now I have another example that will hopefully put it into better perspective as to what Im attempting to do.

=SUM(IF(J.Gutierrez!$F$4:$F$24=(Summary!$AA$14),(J .Gutierrez!$E$4:$E$24))) + SUM(IF(J.Gutierrez!$F$4:$F$24=(Summary!$AA$15),(J. Gutierrez!$E$4:$E$24))) + SUM(IF(J.Gutierrez!$F$4:$F$24=(Summary!$AA$16),(J. Gutierrez!$E$4:$E$24))) + SUM(IF(J.Gutierrez!$F$4:$F$24=(Summary!$AA$17),(J. Gutierrez!$E$4:$E$24))) + SUM(IF(J.Gutierrez!$F$4:$F$24=(Summary!$AA$18),(J. Gutierrez!$E$4:$E$24))) + SUM(IF(J.Gutierrez!$F$4:$F$24=(Summary!$AA$19),(J. Gutierrez!$E$4:$E$24))) + SUM(IF(J.Gutierrez!$F$4:$F$24=(Summary!$AA$20),(J. Gutierrez!$E$4:$E$24))) + SUM(IF(J.Gutierrez!$F$4:$F$24=(Summary!$AA$21),(J. Gutierrez!$E$4:$E$24))) + SUM(IF(J.Gutierrez!$F$4:$F$24=(Summary!$AA$22),(J. Gutierrez!$E$4:$E$24))) + SUM(IF(J.Gutierrez!$F$4:$F$24=(Summary!$AA$23),(J. Gutierrez!$E$4:$E$24)))

EXAMPLE: Now the above formula performs what Im trying to accomplish, but in longhand, and only does it for one day as where I need to total 14 days of the pay period, and I need to do it for 8 other employees. Now creating a formula that is 14 times longer than this seems a bit extreme.

GOAL: Im managing a time card, and this time card has a column for the time, and a dropdown in the next cell for the duty performed during that time frame. Now I want to create a summary report that will filter out and total up any overtime duties selected from the dropdown. In this instance there are 10 Overtime Cells (AA14:AA23)
The longhand version shown above has me copying the formula 10 times over so I can individually designate the 10 overtime cells.

SUM(IF(J.Gutierrez!$F$4:$F$24=(Summary!$AA$14:$AA$ 23),(J.Gutierrez!$E$4:$E$24)))

Thinking I could save myself the trouble I tried to enter this formula above which includes the entire range of cells instead of doing them individually... and logically I feel it should work, but it returns an N/A error that says "value is not available to the formula or function." WHAT GIVES?! I cant figure this out. Can i get some assistance please? Thanks in advance

You know what, I think the TRANSPOSE function did the trick! Ill let you know. Going to try it out

"reznor9" wrote:
Now I have another example that will hopefully put it
into better perspective as to what Im attempting to do.
=SUM(IF(J.Gutierrez!$F$4:$F$24=(Summary!$AA$14),(J .Gutierrez!$E$4:$E$24)))
+
SUM(IF(J.Gutierrez!$F$4:$F$24=(Summary!$AA$15),(J. Gutierrez!$E$4:$E$24)))
+
[....]
+
SUM(IF(J.Gutierrez!$F$4:$F$24=(Summary!$AA$23),(J. Gutierrez!$E$4:$E$24)))

Does the following array-entered formula do what want (press
ctrl+shift+Enter instead of just Enter)?

=SUM(IF(J.Gutierrez!F4:F24=TRANSPOSE(Summary!AA14: AA23),J.Gutierrez!E4:E24))

-----

To understand how the formula works, try the following demonstration in a
new workbook.

Enter a into A1, b into A2 and c into A3. Copy A1:A3 and paste into A4:A9.
The result should be a, b and c repeated 3 times.

Enter 1 into B1, and enter =10*B1 into B2. Copy B2 and paste into B3:B9.
Format B1:B9 as Number with 0 decimal places. Better: format as Custom
000000000.

The transpose command worked like a charm! Saved me from a lot of work.