Conditional filling of a cell

Question

This is not conditional formatting.&nbsp;I would like to ask for advice:I have two cells and I need to insert a function into another cell that automatically fills in predefined text depending on whether both cells meet a certain condition and another text depending on whether only one of the cells meets a certain condition.An example: If both cells contain YES, then the amount 5,000 will appear in the third cell, however, if only one cell contains YES and one contains NO, then only 3,000 will appear in the third cell.Thank you very much for advice!Nikol

riny_van_eekelen · Answer

Nikola_Tokosova&nbsp;As a variant, just for fun:=SWITCH(BYROW(--(D1:E4="yes"),SUM),0,"",1,3000,2,5000)

peterbartholomew1 · Answer

Nikola_Tokosova&nbsp;All that is required is a formula.&nbsp; One such possibility is= IFS(
     AND(condition1="yes", condition2="yes"), 5000,
     OR( condition1="yes", condition2="yes"), 3000,
     TRUE, ""
  )&nbsp;

peterbartholomew1 · Answer

Nikola_Tokosova&nbsp;Another option is simply to count the "yes"s&nbsp;= LET(
    yesCount, COUNTIFS(conditions, "yes"),
    CHOOSE(1 + yesCount, "", 3000, 5000)
  )The formula is expressed using Excel 365 syntax.&nbsp;

sergeibaklan · Answer

Nikola_Tokosova&nbsp;
As variant
=MIN( SUMPRODUCT(--(A1:B1="Yes") )*3000, 5000)

Forum Discussion

Conditional filling of a cell

4 Replies