Search code examples
excelexcel-formulaexcel-2010excel-2007

How to combine below two `ifs` formulas in excel?

How to combine below two ifs formulas in excel?

=IF(AND( Sheet1!BC2<>"", Sheet1!CI2<>"",Sheet1!CA2=""),IF(Sheet1!CI2="","",Sheet1!CI2),IF(Sheet1!CA2="","",Sheet1!CA2)


=IF(AND( Sheet1!BC2="B",Sheet1!CI2<>""),IF(Sheet1!CI2="","",Sheet1!CI2),IF(Sheet1!CA2="","",Sheet1!CA2)

Both formulas are working correct individually .

Actually its a huge excel sheet in first cycle i did filtering and alter the data with Sheet1!BC2<>"",Sheet1!CI2<>"",Sheet1!CA2="" . In next phase I have to filter and to fill the blank data with this condition Sheet1!BC2="B",Sheet1!CI2<>"" . Though they work perfectly alone but how to write it in combination .

Solution

  • [EDITED] Try this:

    =IF(AND( Sheet1!BC2<>"", Sheet1!CI2<>"",Sheet1!CA2=""),IF(Sheet1!BC2="B",Sheet1!CI2,Sheet1!CA2),IF(Sheet1!CA2="","",Sheet1!CA2))