msexcelman
New Member
- Joined
- Jan 24, 2005
- Messages
- 2
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>
"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>