Sum.If function but depending on 3 different columns?

[corne_mo]

I hope someone can help me out here:
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.

 

[Peo Sjoblom]

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


--


Regards,


Peo Sjoblom

 

[corne_mo]

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.

 

[Peo Sjoblom]

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


--


Regards,


Peo Sjoblom

 

[corne_mo]

Many thanks ! )

 

[corne_mo]

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!D114)

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

What am I doing wrong here?

 

[Peo Sjoblom]

Thanks for the feedback


--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

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!D165535)

or

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


--


Regards,


Peo Sjoblom