nested if statements with sum

cblake

New Member
Joined
Oct 23, 2002
Messages
3
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

RichardS

Well-known Member
Joined
Feb 16, 2002
Messages
761
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
Joined
Aug 4, 2002
Messages
851
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
Joined
Mar 12, 2002
Messages
11,454
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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.
 

Forum statistics

Threads
1,143,702
Messages
5,720,374
Members
422,280
Latest member
scottylan

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