Forum Discussion

Copper Contributor

Jun 23, 2023

if function for text and value

can you help me please to formulate this function i want to use if function and at the end result want to sum the values like if the word "yes" in there in the cell b2 then write the valu...

Formulas and Functions

SergeiBaklan

Diamond Contributor

Jun 23, 2023

AmalAlhadyab

It could be

=SUMPRODUCT( --(B2:B4="Yes" ) )

AmalAlhadyab
Copper Contributor
Jun 24, 2023
SergeiBaklan thank you a lot it is really helpful
but what if the value for Yes I wanted to be +2, No -1
which formula i can use?
- SergeiBaklan
  Diamond Contributor
  Jun 24, 2023
  AmalAlhadyab
  
  As variant
  
  =SUMPRODUCT( 2*(B2:B4="Yes") - (B2:B4="No") )
- bosinander
  Iron Contributor
  Jun 24, 2023
  AmalAlhadyab Using IFS() you may define the values quite freely. The last one is set to true to catch all that has not matched earlier in the formula.
  =SUM(IFS(B2:B4="Yes", 2, B2:B4="No", -1, TRUE, 0))
  ,