Forum Discussion

helpmeplease6's avatar
helpmeplease6
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 Column E, but ONLY if there are no conflicting values in the other cells. If there is a discrepancy between cells I want to display ERROR. If there are no values in any cells I want to display n/a.

Thanks in advance for you help!

excel
Formulas and Functions

2 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Mar 25, 2023

    helpmeplease6 

     

    = BYROW(voted, Partyλ)

Partyλ
= LAMBDA(v, 
    LET(
      party, UNIQUE(FILTER(v, v <> "", "n/a"), 1),
      consistent, COUNTA(party) = 1,
      IF(consistent, party, "ERROR")
    )
  )

     

    Don't see why changing party should be an error though.  Henry could be seen as the sanest cookie in the jar!

     

    This is for 365 (or 2021) only.

  • OliverScheurich's avatar
    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.

Resources