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

[Guest]

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

 

[Guest]

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

 

[Guest]

Hi Dave,

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

Thanks,

Hongguang

 

[Ragdyer]

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))&":"&ADDRESS(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)))