How do I use "offset" function in "array formula"?
 

A B C D E F G H I J
K L
1 1 2 3 4 5 6 7 8 9 10
11 12
2 10 20 30 40 50 60 70 80 90 100
110 120
My real purpose is to sum 4 cells in the second row where the cell above the
first cell satisfies a condition. In other word, I want 50+60+70+80. I do not
understand why the 3rd formula does not work, and why 1st and 2nd do not have
the same value. I used the array formula.
{=SUM(IF(A1:L1=5,OFFSET(A2:L2,0,0),""))} 50
{=SUM(IF(A1:L1=5,OFFSET(A2:L2,0,0,1,1),""))} 10
{=SUM(IF(A1:L1=5,OFFSET(A2:L2,0,0,1,4),""))} #N/A
Thanks,
Hongguang

How do I use "offset" function in "array formula"?
 

I'll hazard a guess here and say that you are at once trying to sum based on
a condition, which condition is only met in one column, and summing across a
range four columns wide?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"hongguang" wrote:

A B C D E F G H I J
K L
1 1 2 3 4 5 6 7 8 9 10
11 12
2 10 20 30 40 50 60 70 80 90 100
110 120
My real purpose is to sum 4 cells in the second row where the cell above the
first cell satisfies a condition. In other word, I want 50+60+70+80. I do not
understand why the 3rd formula does not work, and why 1st and 2nd do not have
the same value. I used the array formula.
{=SUM(IF(A1:L1=5,OFFSET(A2:L2,0,0),""))} 50
{=SUM(IF(A1:L1=5,OFFSET(A2:L2,0,0,1,1),""))} 10
{=SUM(IF(A1:L1=5,OFFSET(A2:L2,0,0,1,4),""))} #N/A
Thanks,
Hongguang

How do I use "offset" function in "array formula"?
 

Hi Dave,

You are right. The original formula is more complicated than this one but
the purpose is similar.

Thanks,

Hongguang

"Dave F" wrote:

I'll hazard a guess here and say that you are at once trying to sum based on
a condition, which condition is only met in one column, and summing across a
range four columns wide?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"hongguang" wrote:

A B C D E F G H I J
K L
1 1 2 3 4 5 6 7 8 9 10
11 12
2 10 20 30 40 50 60 70 80 90 100
110 120
My real purpose is to sum 4 cells in the second row where the cell above the
first cell satisfies a condition. In other word, I want 50+60+70+80. I do not
understand why the 3rd formula does not work, and why 1st and 2nd do not have
the same value. I used the array formula.
{=SUM(IF(A1:L1=5,OFFSET(A2:L2,0,0),""))} 50
{=SUM(IF(A1:L1=5,OFFSET(A2:L2,0,0,1,1),""))} 10
{=SUM(IF(A1:L1=5,OFFSET(A2:L2,0,0,1,4),""))} #N/A
Thanks,
Hongguang

How do I use "offset" function in "array formula"?
 

With your criteria (5) for Row1 entered in A3,
Would you care to play with something like this:

=SUM(INDIRECT(ADDRESS(2,MATCH(A3,A1:L1,0))&":"&ADD RESS(2,MATCH(A3,A1:L1,0)+3)))

You must remember that Match() is relative, so if you don't start in Column
A, you'll have to adjust the references to accommodate the offset from
Column A,
and the first argument of Address() should reference the row number to be
returned (summed).

For example, if your range was F10 to Q11, try:

=SUM(INDIRECT(ADDRESS(11,MATCH(A3,F10:Q10,0)+5)&": "&ADDRESS(11,MATCH(A3,F10:Q10,0)+8)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"hongguang" wrote in message
...
Hi Dave,

You are right. The original formula is more complicated than this one but
the purpose is similar.

Thanks,

Hongguang

"Dave F" wrote:

I'll hazard a guess here and say that you are at once trying to sum based
on
a condition, which condition is only met in one column, and summing
across a
range four columns wide?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"hongguang" wrote:

A B C D E F G H I
J
K L
1 1 2 3 4 5 6 7 8 9
10
11 12
2 10 20 30 40 50 60 70 80 90 100
110 120
My real purpose is to sum 4 cells in the second row where the cell
above the
first cell satisfies a condition. In other word, I want 50+60+70+80. I
do not
understand why the 3rd formula does not work, and why 1st and 2nd do
not have
the same value. I used the array formula.
{=SUM(IF(A1:L1=5,OFFSET(A2:L2,0,0),""))} 50
{=SUM(IF(A1:L1=5,OFFSET(A2:L2,0,0,1,1),""))} 10
{=SUM(IF(A1:L1=5,OFFSET(A2:L2,0,0,1,4),""))} #N/A
Thanks,
Hongguang