• 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 = ""




  • 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


  • 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


    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.


    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).

  • Re: If statements


    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)


  • Re: If statements


    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

    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!


  • 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.

Participate now!

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