Why cant I expand the equations parameters without error?
 

Im trying to setup a formula that will add up values of a row of cells IF the dropdown associated with the cell is of a certain value.(ie: Overtime Cells)

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."
what gives?
I feel like it should work the same with the exception that it is also adding up values if the cells fall under the additional... what am i missing?

Why cant I expand the equations parameters without error?
 

"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),...)

Why cant I expand the equations parameters without error?
 

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

Why cant I expand the equations parameters without error?
 

"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.