Sumproduct with offset?
 

Hello everyone,

I have a large data where I need to sum certain values based on the
criteria various rows above or below. For example, I need to sum the
values of C one row down the criteria, A=1 and B=1 (sum of 6 and 5 in
column C).

A B C
2 3 2
1 1 3
4 3 6
1 1 4
2 5 5

I have tried to combine sumproduct with the offset function, but I just
can't get it to work. Thanks.

-matt

Hi MJ

How about
=SUMPRODUCT(--(A1:A5=1),--(B1:B5=1),C2:C6)

Cheers
JulieD

"MJ" wrote in message
...
Hello everyone,

I have a large data where I need to sum certain values based on the
criteria various rows above or below. For example, I need to sum the
values of C one row down the criteria, A=1 and B=1 (sum of 6 and 5 in
column C).

A B C
2 3 2
1 1 3
4 3 6
1 1 4
2 5 5

I have tried to combine sumproduct with the offset function, but I just
can't get it to work. Thanks.

-matt

That simple?!
Thanks JulieD, works fine.

-matt


JulieD wrote:
Hi MJ

How about
=SUMPRODUCT(--(A1:A5=1),--(B1:B5=1),C2:C6)

Cheers
JulieD

"MJ" wrote in message
...

Hello everyone,

I have a large data where I need to sum certain values based on the
criteria various rows above or below. For example, I need to sum the
values of C one row down the criteria, A=1 and B=1 (sum of 6 and 5 in
column C).

A B C
2 3 2
1 1 3
4 3 6
1 1 4
2 5 5

I have tried to combine sumproduct with the offset function, but I just
can't get it to work. Thanks.

-matt

Hi Matt

just needed a touch of "lateral" thinking :) - glad it solved your problem

Cheers
JulieD

"MJ" wrote in message
...
That simple?!
Thanks JulieD, works fine.

-matt


JulieD wrote:
Hi MJ

How about
=SUMPRODUCT(--(A1:A5=1),--(B1:B5=1),C2:C6)

Cheers
JulieD

"MJ" wrote in message
...

Hello everyone,

I have a large data where I need to sum certain values based on the
criteria various rows above or below. For example, I need to sum the
values of C one row down the criteria, A=1 and B=1 (sum of 6 and 5 in
column C).

A B C
2 3 2
1 1 3
4 3 6
1 1 4
2 5 5

I have tried to combine sumproduct with the offset function, but I just
can't get it to work. Thanks.

-matt