Why cant I expand the equations parameters without error?

[reznor9]

Im trying to setup a formula that will add up values of a row of cell
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.Adams!$E$4:$E$24)))

The forumla above only adds up one of 10 possible overtime values. no
when I try to extend the parameters of the equasion to include all 1
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 als
adding up values if the cells fall under the additional... what am
missing

 

[joeu2004]

If I insert this formula it works fine.
=SUM(IF(J.Adams!$F$4:$F$24=(Summary!$AA$14),(J.Adams!$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),...)

 

[joeu2004]

=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$14:$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.

 

[reznor9]

;1609273']I 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 (pres

ctrl+shift+Enter instead of just Enter):

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

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

Been a while since I worked on this and I still have not figured i
out.
Yes I am array entering the formula.
Now I have another example that will hopefully put it into bette
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 tota
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 bi
extreme.

GOAL: Im managing a time card, and this time card has a column for th
time, and a dropdown in the next cell for the duty performed during tha
time frame. Now I want to create a summary report that will filter ou
and total up any overtime duties selected from the dropdown. In thi
instance there are 10 Overtime Cells (AA14:AA23)
The longhand version shown above has me copying the formula 10 time
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 formul
above which includes the entire range of cells instead of doing the
individually... and logically I feel it should work, but it returns a
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 advanc

 

[joeu2004]

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.

Enter a into C1, b into C2 and c into C3.

In A11, array-enter the following incorrect formula (press ctrl+shift+Enter
instead of just Enter):

=SUM(IF(A1:A9=C1:C3,B1:B9))

Note that the Excel returns the #N/A error. Isn't that equivalent to what
you are doing?

Now in A11, array-enter the correct formula:

=SUM(IF(A1:A9=TRANSPOSE(C1:C3),B1:B9))

You should see 111111111. That demonstrates that it summed B1:B9
corresponding to A1:A9 that matches each of C1:C3. Isn't that equivalent to
what you want?

You might want to step through the calculation of that formula using the
Evaluate Formula operation.

As a further demonstration, put x into several of A1:A9. Notice how A11
changes, reflecting 0 in the positions corresponding to the x's. Isn't that
equivalent to what you want?

You might want to step through the calculation of that formula using the
Evaluate Formula operation.

The key to making the formula work is the SUM can operate over a
2-dimensional array.

-----

If the array-entered SUM/TRANSPOSE formula is not what you want, I suggest
that you upload an example Excel file that demonstrates the problem,
removing or changing any identifying private data first, to a file-sharing
website.

Then post the "shared", "public" or "view-only" link (aka URL; http://...)
in a response here.

The following is a list of some free file-sharing websites; or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com

 

[reznor9]

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