Sum.If function but depending on 3 different columns?
 

I hope someone can help me out he
I have 2 worksheets:
WS1
WS2

On WS2 I need to sum all values of column D on WS1 that have:
WS1 column A = 1
WS1 column B = 3
WS1 column C <= 7

Is this possible and if yes, how can I accomplish this?

Thanks in advance.

Sum.If function but depending on 3 different columns?
 

=SUMPRODUCT(--(A2:A200=1),--(B2:B200=3),--(C2:C200<=7),D2:D200)


--


Regards,


Peo Sjoblom



"corne_mo" wrote in message
...
I hope someone can help me out he
I have 2 worksheets:
WS1
WS2

On WS2 I need to sum all values of column D on WS1 that have:
WS1 column A = 1
WS1 column B = 3
WS1 column C <= 7

Is this possible and if yes, how can I accomplish this?

Thanks in advance.

Sum.If function but depending on 3 different columns?
 

Hi Peo,

just did a very small test and it looks like it works although the
sumproduct function isn't made for this purpose as far as I know.
Can you explain me the function you give above, including the -- part?
Thanks in advance.

Sum.If function but depending on 3 different columns?
 

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


--


Regards,


Peo Sjoblom



"corne_mo" wrote in message
...
Hi Peo,

just did a very small test and it looks like it works although the
sumproduct function isn't made for this purpose as far as I know.
Can you explain me the function you give above, including the -- part?
Thanks in advance.

Sum.If function but depending on 3 different columns?
 

Many thanks ! :-))

Sum.If function but depending on 3 different columns?
 

Still one questions though:
I tried this over multiple worksheets, where the table is on worksheet1 and
the formula in worksheet 2:
=SUMPRODUCT(--(WS1!A1:A14=1);--(WS1!B:B="b");--(WS!C:C<9);WS1!D1:D14)

The following statements give errors:
--(WS1!B:B="b")
--(WS!C:C<9)

What am I doing wrong here?

Sum.If function but depending on 3 different columns?
 

Thanks for the feedback


--


Regards,


Peo Sjoblom



"corne_mo" wrote in message
...
Many thanks ! :-))

Sum.If function but depending on 3 different columns?
 

You can not use the whole column in array formulas or formulas that work
like array formulas
also the ranges need to be of equal size

maybe something like

=SUMPRODUCT(--(WS1!A1:A65535=1);--(WS1!B1:B65535="b");--(WS1!C1:C65535<9);WS1!D1:D65535)

or

=SUMPRODUCT(--(WS1!A1:A14=1);--(WS1!B1:B14="b");--(WS1!C1:C14<9);WS1!D1:D14)


--


Regards,


Peo Sjoblom



"corne_mo" wrote in message
...
Still one questions though:
I tried this over multiple worksheets, where the table is on worksheet1
and
the formula in worksheet 2:
=SUMPRODUCT(--(WS1!A1:A14=1);--(WS1!B:B="b");--(WS!C:C<9);WS1!D1:D14)

The following statements give errors:
--(WS1!B:B="b")
--(WS!C:C<9)

What am I doing wrong here?