CODEWITHSUNDEEP
excelif-statementnested
GiANTOnFire
GiANTOnFire

Reputation: 203

Nested IF statements with AND in excel

I have a column (C) in Excel that looks like this: columns

I want the bottom cell (currently green and displaying 1) to: display 2 if sum of c2:c14 = 13 and c16 = 1 display 1 if sum of c2:c14 >= 7 and c16 = 1 else display 0

This is what I have and it is saying it is an invalid formula, which I am sure it is, I just can't work out where I have gone wrong... 

=IF(AND(SUM(C2:C14=13,C16=1),2)),IF(AND(SUM(C2:C14)>=7,C16=1,1),0)

Anyone able to help?!

Upvotes: 0

Views: 73

Answers (2)

Gi0rgi0s
Gi0rgi0s

Reputation: 1877

You have a few syntax errors.

AND(SUM(C2:C14=13,C16=1))

Should be

AND(SUM(C2:C14)=13,C16=1))

you don't want to evaluate anything inside of the sum.

The 2 should be outside of your AND:

IF (AND(SUM(C2:C14)=13,C16=1),2,...)

Your second if statement should be nested in the first do it acts like an if else statement, and again your desired result of 1 should be outside of your AND.

This could work:

=IF(AND(SUM(C2:C14)=13,C16=1),2,IF(AND(SUM(C2:C14)>=7,C16=1),1,0))

Upvotes: 0

Scott Holtzman
Scott Holtzman

Reputation: 27259

You just had the parenthesis in the wrong place :)

=IF(AND(SUM(C2:C14)=13,C16=1),2,IF(AND(SUM(C2:C14)>=7,C16=1),1,0))

This can be checked by ensuring that each function has a closing parens immediately after the arguments are entered. For example, in SUM(C2:C14=13, the SUM function only takes range references as arguments, so a closing parens is needed directly after the C14.

Upvotes: 1

Related Questions