I have a column (C) in Excel that looks like this:
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?!
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.
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))
