nested if statements with sum

[cblake]

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.

 

[RichardS]

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]

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]

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
	A	B	C	D	E	F
1	1	2	3	4
2	2	2	2	4		8
3	3	3	3	4
4	1	1	1	4		8
5	2	2	2	4
6	1	2	3	4
7	2	2	2	4
8	3	3	3	4
9	1	1	1	4
10	2	2	2	4
11	1	1	1	4
12	2	2	2	4
13	3	3	3	4
14	1	1	1	4
15	2	2	2	4
16	1	1	1	4
17	2	2	2	4
18	3	3	3	4
19	1	1	1	4
20	2	2	2	4
21
22
23
24
25	1	2	3
Sheet3

</SPAN>

Regards!

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

 

[Aladin Akyurek]

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]

Thank-you all for your generous help.

cblake

 

[Peterfc2]

I get 180 not 8