A challenge: referencing cell contents in nested function formula

[Guest]

Here is the formula I have written. Obviously it does not work. I would
sure appreciate any assistance I can get in making it a valid formula.
PLEASE!!!!!

=Sum(IF('Worksheet2'!A1:A10=(Cell("contents",A474+1)),'Worksheet2'!J1:J8))

My attempt? To create a formula which
references the next cell AFTER this formula (A474+1)
takes the contents of that cell (Cell("contents", )
asks if the contents of that cell are the same as the contents of ANY
cell in
another Worksheet IF(Worksheet2'A1:A10=
If it does then SUM the contents of another range of cells
,('Worksheet2!J1:J8)

What am I not getting right?

Please Help

 

[RagDyer]

Just what are you trying to reference with <<<"Cell("contents",A474+1)">>>?

To use the value in a cell, *just reference the cell*!
You *don't* need the Cell() function.

=Sum(IF('Worksheet2'!A1:A10=A474+1,'Worksheet2'!J1:J8))

Now, *even with this formula*, you're using 2 different size ranges.
You're polling 10 cells (A1 to A10), and calculating only 8 cells (J1 to
J8).

If A1 to A10 contained the values 1 to 10 respectively,
And A474 contained 8, so that A474+1 returned 9,
Since you're *only* referencing J1 to J8 (8 cells), the formula would return
an error.
A9 and A10 are superfluous in this formula.

And *finally*, do you realize that this is an *array* formula?

 

[RagDyer]

BTW,

You might try this *non*array formula:

=SUMIF('Worksheet2'!A1:A10,A474+1,'Worksheet2'!J1:J8)

The caveat about the *uneven* range sizes still prevails.

 

[RagDyer]

Correction!

The caveat about the *uneven* range sizes *does not* prevail with SUMIF().

In this case, if values do exist *beyond* J8, they *will* be returned.