# If statements

• Hi again,

I'm doing these IF statements and I was wondering if there's a way to put a double if statement? Like

If A1 = 0 and B1 = 0 then C1 = 0
If A1 = 1 and B1 = 1 then C1 = 1
If A1 = 1 and B1 = 0 then C1 = 0
If A1 = 0 and B1 = 1 then C1 = 0
If A1 and/or B1 = "" then C1 = ""

Anyone?

Thanks

Sue

• Re: If statements

Try this. Place in C1 and copy down.

=IF(OR(LEN(A1)=0,LEN(B1)=0),"",IF(MIN(A1:B1)=0,0,1))

• Re: If statements

Sue,
Tom has shown how to use the Or function, in a very tidy solution. Just so that you are aware though, you can also use the And function.

=And(Condition1,Condition2,.....)

Alan.

• Re: If statements

Copy this statement and paste into C1

=IF(AND(A1=0,B1 = 0),0,IF(AND(A1=1,B1 = 1),1,IF(AND(A1=1,B1 = 0),0,IF(AND(A1=0,B1 = 1),0,""))))

Hope this helps

Matt B

• Re: If statements

Hi Guys!

Ok..the first one worked but didn't work. It gave me blanks when B1 was blank, but zeros then A1 was blank. I C1 to be blank if there's a blank in A1 and/or B1. It also didn't give me a 1 in C1 when there was a 1 in A1 and 1 in B1.

The second one didn't work also. No values came up in C1 at all.

I've gone through and done it manually as time has run out on this..but I'd still appreciate learning how to do it using the IF statement. Thanks

Sue

• Re: If statements

Try this in C1:

=IF(OR(A1="",B1=""),"",--AND(A1:B1))

• Re: If statements

That worked for the blanks. I'll see if I can put it all together now. Thanks.

• Re: If statements

I've attached a workbook with an example if you're interested.

You might also consider using the sumproduct function if you are testing condition. I have found it very useful and saves a ton of time

=sumproduct((column1="")*(column2=""))

if both conditions are met, the formula return 1, if one is met, it returns 0, if neither are met, the formula returns 0.

You can use this very effectively for large data dumps (17000+ rows by 20 columns).

I have a spreadsheet that takes headcount by business unit and multiples it by the salary to calculate the salary by business unit.

=sumproduct((Region="US")*(BU="X")*Salary)

where Region, BU & Salary are named ranges on the table.

Give it a try. This is one of the biggest time savers I have discovered (with the helps of this board and the excel hacks book).

## Files

:thanx: Tony

• Re: If statements

Quote

Ok..the first one worked but didn't work. It gave me blanks when B1 was blank, but zeros then A1 was blank

Your sample sheet did not provide an example of this, but my guess is that your column A cell was not blank. It may have displayed blank, but I'll bet there was a space or other non-printing character entered.

See if the follwoing makes any difference. (The TRIM removes leading/trailing spaces)

=IF(OR(LEN(TRIM(A1))=0,LEN(TRIM(B1))=0),"",IF(MIN(A1:B1)=0,0,1))

• Re: If statements

Tom,

I attached the workbook to help Suzanne but it looks like your post is addressed to me. Just wanted to let everyone know that the workbook I put out there is not the one she was having problems with.

Thanks though...

:thanx: Tony

• Re: If statements

Oops! Sorry, read too fast and didn't focus on the poster's name.

My last post was meant for Suzanne.

• Re: If statements

Hi Thom,

Actually the blank scoring critera was the last line of my explanation..it was no tmeant to a sample "sheet" per se....rather was just the way I chose to describe the scoring I needed to do.

That second one worked but didn't work. It fixed the blanks (and I think I know why the "blanks" weren't blanks before..I used an IF statement to change those fields from YES or NO answers to 1 and 0..and put if it was "" then keep it ""..which I"m now assuming puts in a space?.

Anyways..it doesn't work because it does not give me a 1 in C1 when there is a 1 in A1 and A2.

ASCalese..thanks for the sheet. I won't get the chance to try it today but I"ll let you know how it goes!

Sue

• Re: If statements

Actually, if your output cell is to have a "1" in it only if both A1 and B1 have 1's, then the following may work:

=IF(AND(A1=1,B1=1),1,"Not Both Ones")

Change the "Not Both Ones" to whatever you want.

• Re: If statements

Oops ... forgot you wanted a 0 if there was one in A or B. THe following may get there.

=IF(AND(A1=1,B1=1),1,IF(AND(OR(A1=0,B1=0),LEN(A1)>0,LEN(B1)>0),0,"Not Both Ones"))

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!