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.

=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.

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.

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.

Many thanks ! :-))

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?

Thanks for the feedback


--


Regards,


Peo Sjoblom



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

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?