# Getting the SUM that meets more than 1 conditions

#### msexcelman

##### New Member
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.

<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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### ken2step

##### Well-known Member
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
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
Thanks for catching the wrong starting position on the ranges Fergus! I was just copying his formula....should of looked closer.

#### msexcelman

##### New Member
Many thanks.

May I know what does --() means?

#### chiello

##### Well-known Member
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()

Replies
10
Views
563
Replies
0
Views
2K
Replies
4
Views
164
Replies
3
Views
681
Replies
2
Views
448

1,181,257
Messages
5,928,977
Members
436,637
Latest member
pcpc

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

### Which adblocker are you using?

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

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