Help with nested IF statement in Excel XP 1

[LissaB]

Okay- It has been a while since I have used any IF statements in Excel and I've never successfully gotten a nested IF statement to work.

So here it goes- I have column C that contains a dollar amount, column D that contains a name and column I that contains either an X or is null. What I need to do is say if column I contains an X AND column D contains a certain name ex: Michelle then add the take those rows in column C and add them together.

Here is what I have tried:
=SUM(IF((I2:I35="X&quot+(D235="Michelle&quotC2:c35)

I know I am missing some parenthases (sp?) and probably some other technical stuff, but I am really kind of desperate and have been banging my head on my desk trying to figure this out!!!! Please help!!!!

Thanks so much!!!

Lissa

 

[KenWright]

The SUMPRODUCT feature might be easier to understand.

=SUMPRODUCT((I2:I35="X&quot*(D235="Michelle&quot*(C2:c35))


The formula exploits the fact that Excel interprets TRUE as 1 and FALSE as 0. Take the formula below:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A&quot*(C9:C20))

This sets up an array that gives you something that looks like this (depending on the variables of course):-

A B C
9 TRUE * FALSE * 3
10 FALSE * FALSE * 4
11 TRUE * TRUE * 2
12 TRUE * TRUE * 1
13 TRUE * FALSE * 4
14 TRUE * TRUE * 3
15 TRUE * TRUE * 2
16 FALSE * TRUE * 8
17 TRUE * TRUE * 6
18 TRUE * TRUE * 8
19 TRUE * TRUE * 7
20 TRUE * TRUE * 6

Which because TRUE=1 and FALSE=0, is interpreted as:-

A B C
9 1 * 0 * 3 = 0
10 0 * 0 * 4 = 0
11 1 * 1 * 2 = 2
12 1 * 1 * 1 = 1
13 1 * 0 * 4 = 0
14 1 * 1 * 3 = 3
15 1 * 1 * 2 = 2
16 0 * 1 * 8 = 0
17 1 * 1 * 6 = 6
18 1 * 1 * 8 = 8
19 1 * 1 * 7 = 7
20 1 * 1 * 6 = 6
-------------
35

and the SUM bit just adds up all the end values of the products

Regards
Ken................

 

[Zathras]

This is also a prime candidate for using database techniques. Assuming the database is set up this way:

A1: 'Aaaaaaa
B1: 'Bbbbbbb
C1: 'Amount
D1: 'Name
E1: 'Eeeee
F1: 'Fffff
G1: 'Ggggg
H1: 'Hhhhh
I1: 'Select

Then create a criteria range like this:
[blue]

K1: 'Name
L1: 'Select
K2: 'Michelle
L2: 'X

[/color]
Then this simple formula gives you the total you want:
[blue]

  =DSUM($A$1:$I$35,3,$K$1:$L$2)

[/color]
Which would be even simpler with range names:
[blue]

  =DSUM(DATABASE,3,CRITERIA)

[/color]
To take it one step further: You could put data validation on Cell K2 with a drop-down list to allow the user to pick which name to totalize.

 

[LissaB]

Ken-
Thank you so much- this worked perfectly!!!

Lissa