Forum Discussion

Copper Contributor

Mar 25, 2023

How to: display value if ANY cell within a row has a non-zero value?

I’m looking for a formula to put in Column E that will automatically look at the cells in Columns B-C-D of the same row and IF there is a value in ANY of those cells, the value will be displayed in C...

excel

Formulas and Functions

OliverScheurich

Gold Contributor

Mar 25, 2023

helpmeplease6

=IF(COUNTBLANK(B3:D3)=3,"n/a",
IF(AND(COUNTIF(B3:D3,"A")>0,COUNTIF(B3:D3,"S")>0),"error",
IF(AND(COUNTIF(B3:D3,"A")>0,COUNTIF(B3:D3,"S")=0),"A",
IF(AND(COUNTIF(B3:D3,"A")=0,COUNTIF(B3:D3,"S")>0),"S",""))))

You can try a nested IF in e.g. Excel 2013. If you have Office 365 or Excel 2021 you can simplify with formulas such as UNIQUE.