Getting the SUM that meets more than 1 conditions

[msexcelman]

Suppose I have 2 worksheets (see below). Sheet1 contains the raw data.
"Subtotal" in Sheet2 is to be derived from Sheet1.

I would like to find out the subtotal of "Value" for rows that have the same
values on columns Col1, Col2 and Col3. The subtotals are to be place on Column
D in Sheet2.

How can I get the subtotals in Excel? I tried the function below but it
doesn't seem to work. Any pointer would be greatly appreciated.

Thanks in advance.

<pre>
=SUM(IF((Sheet1!A:A=Sheet2!A2) * (Sheet1!B:B=Sheet2!B2) *
(Sheet1!C:C=Sheet2!C2), Sheet1!D:D, 0))

Sheet1
| A B C D
--+------------------------------
1 | Col1 Col2 Col3 Value
2 | A B C 1
3 | A B C 3
4 | M N O 4
5 | X Y Z 34
6 | M N O 5
7 | A B C 10
8 | M N O 6
...

Sheet2
| A B C D
--+---------------------------------
1 | Col1 Col2 Col3 Subtotal
2 | A B C xxx
3 | M N O yyy
4 | X Y Z zzz
</pre>

 

[ken2step]

SUMPRODUCT((Sheet1!A1:A8=Sheet2!A2) * (Sheet1!B1:B8=Sheet2!B2) *
(Sheet1!C1:C8=Sheet2!C2)*(Sheet1!D1:D8))

Note you can not have the whole column like A:A, must have a defined range and all the ranges in A-D have to be the exact same size.

 

[Fergus]

Hi,

In Sheet2, D2 =SUMPRODUCT(--(Sheet1!$A$2:$A$8=A2),--(Sheet1!$B$2:$B$8=B2),--(Sheet1!$C$2:$C$8=C2),Sheet1!$D$2:$D$8) and copy down.

HTH

 

[ken2step]

Thanks for catching the wrong starting position on the ranges Fergus! I was just copying his formula....should of looked closer.

 

[msexcelman]

Many thanks.

May I know what does --() means?

 

[chiello]

May I know what does --() means?

It is like (-1)*(-1)*(), which converts from boolean to numeric values. The same can be achieved also with 1*() and N()

 

[Aladin Akyurek]

May I know what does --() means?

It is like (-1)*(-1)*(), which converts from boolean to numeric values. The same can be achieved also with 1*() and N()

It's rather:

--(TRUE) ==> -(-1) ==> 1

or

--(1) ==> -(-1) ==> 1

That is, it's not assumed to be a multiplication at the machine level.

See:

http://tinyurl.com/3tty7

http://www.mrexcel.com/board2/viewtopic.php?t=73205