You are here:

Excel/If formula with 2 conditions

Advertisement


Question
QUESTION: Hi, I want to input a formula so that in cell S14 it will either give a 1 or stay blank. For it to give a 1 it must meet criteria. This criteria is C14=x and R14=x.

I tried this formula =IF(AND(C14=x,R14=x),"1","") but it keeps saying #NAME?

Can you help me please?

ANSWER: Hi Lauren!
How are you?


Please try the following

=IF(AND(C14="x",R14="x"),1,"")



x is different from "x"

(1)
"x"  (between quotes) is the letter x

All data - letters or numbers - that you do not use for arithmetic operations have to be delimiter by quotes.


When
=IF(AND(C14="x",R14="x"),1,"")
is executed it compares the content of C14 with the letter x

(2)
x  (without quotes) is a variable and refers to the value it represents

eg. if you give the name x to a cell and this cell has the value 6, when the formula

=IF(AND(C14=x,R14=x),1,"")

is executed it becomes


=IF(AND(C14=6,R14=6),1,"")


Cheers
Adelaide



---------- FOLLOW-UP ----------

QUESTION: thank you!

Also I am linking this spread sheet to another using a =SUMIFS formula but when I input the range which is column S (the column I have input this formula) it isn't adding the cells that has a 1 and has met the required conditions.

Do you know a way to solve this?

Thanks,
Lauren

Answer
Hi Lauren!
How are you?

actually you do not need to use SumIF for summng the 1s of column S as this has only 1 or blank but if you want to see how to use sumif, please try the following

=Sumif(C2:C14,"x",S2:S14)

or
=Sumifs(S2:S14,C2:C14,"x",R2:R14,"x")

Cheers
Adelaide  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Adelaide carvalho

Expertise

I will be able to answer any question on Excel and Visual Basic for Applications - class modules, recursive procedures and functions are my favourite

Experience


Past/Present Clients
Several readers of my Books think they are useful.

©2016 About.com. All rights reserved.