# nested if statements with sum

#### cblake

##### New Member
I'm trying to get the proper format for a SUM formula with 3 conditional IF statements. Is it this?

=SUM(IF(\$A\$1:\$A\$20=A25,IF(\$B\$1:\$B\$20=B25,IF(\$C\$1:\$C\$20=C25))),\$D\$1:\$D\$20)

The idea is to sum figures in column D, if 3 conditions are met.

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### RichardS

##### Well-known Member
Try,

=IF(SUM(\$A\$1:\$A\$20)=A25,IF(SUM(\$B\$1:\$B\$20)=B25,IF(SUM(\$C\$1:\$C\$20)=C25,SUM(\$D\$1:\$D\$20),0),0),0)

There's probably a better way, but it'll work.

Richard

#### BigC

##### Well-known Member
Also try

=IF(AND(SUM(\$A\$1:\$A\$20)=A25,SUM(\$B\$1:\$B\$20)=B25,SUM(\$C\$1:\$C\$20)=C25),SUM(\$D\$1:\$D\$20),0)

hth

#### Yogi Anand

##### MrExcel MVP
Hi cblake:

Welcome to the Board!

If I have understood you correctly, you can use the following SUMPRODUCT formula:

'=SUMPRODUCT((A1:A20=A25)*(B1:B20=B25)*(C1:C20=C25)*(D1:D20))

or the following array-based SUM formula using nested IFs:

'{=SUM(IF(A1:A20=A25,IF(B1:B20=B25,IF(C1:C20=C25,D1:D20,0))))}

see the worksheet simulation wherein I have depicted use of both formulas ...
Part7.xls
ABCDEF
11234
222248
33334
411148
52224
61234
72224
83334
91114
102224
111114
122224
133334
141114
152224
161114
172224
183334
191114
202224
21
22
23
24
25123
Sheet3
</SPAN>

Regards!

Yogi Anand
This message was edited by Yogi Anand on 2002-10-25 02:48

##### MrExcel MVP
On 2002-10-24 23:52, cblake wrote:
I'm trying to get the proper format for a SUM formula with 3 conditional IF statements. Is it this?

=SUM(IF(\$A\$1:\$A\$20=A25,IF(\$B\$1:\$B\$20=B25,IF(\$C\$1:\$C\$20=C25))),\$D\$1:\$D\$20)

The idea is to sum figures in column D, if 3 conditions are met.

=SUM(IF(\$A\$1:\$A\$20=A25,IF(\$B\$1:\$B\$20=B25,IF(\$C\$1:\$C\$20=C25,\$D\$1:\$D\$20))))

or, without IFs,

=SUM((\$A\$1:\$A\$20=A25)*(\$B\$1:\$B\$20=B25)*(\$C\$1:\$C\$20=C25),\$D\$1:\$D\$20)

each array-entered (using control+shift+enter).

Equivalently,

=SUMPRODUCT((\$A\$1:\$A\$20=A25)*(\$B\$1:\$B\$20=B25)*(\$C\$1:\$C\$20=C25),\$D\$1:\$D\$20)

which is normally entered.

#### cblake

##### New Member
Thank-you all for your generous help.

cblake

I get 180 not 8

Replies
5
Views
179
Replies
3
Views
103
Replies
1
Views
32
Replies
5
Views
94
Replies
1
Views
114

1,181,459
Messages
5,930,048
Members
436,717
Latest member
morten_lykke

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