Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions | |||
SUMPRODUCT using offset from ROW if X marks the spot | Excel Worksheet Functions |