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" <moom@sci.fi> wrote in message
news:2BAZd.524$RP3.77@reader1.news.jippii.net...
> 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" <moom@sci.fi> wrote in message
> news:2BAZd.524$RP3.77@reader1.news.jippii.net...
>
>>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" <moom@sci.fi> wrote in message
news:HZCZd.578$qv5.278@reader1.news.jippii.net...
> 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" <moom@sci.fi> wrote in message
>> news:2BAZd.524$RP3.77@reader1.news.jippii.net...
>>
>>>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
>>
>>