Getting the SUM that meets more than 1 conditions

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>
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
632
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

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
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

Well-known Member
Joined
Jan 9, 2003
Messages
632
Thanks for catching the wrong starting position on the ranges Fergus! I was just copying his formula....should of looked closer.
 

msexcelman

New Member
Joined
Jan 24, 2005
Messages
2

ADVERTISEMENT

Many thanks.

May I know what does --() means?
 

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
msexcelman said:
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

Forum statistics

Threads
1,148,056
Messages
5,744,548
Members
423,882
Latest member
Seeham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top