Sum based on Text

[yyasin]

Hello,

I'm having a lot of trouble with this one so hoping someone can help.

I need a formula to sum a range of cells using the weighting below. I'd really appreciate any help with this.

1	Needs Development
2	Basic
3	Intermediate
4	Advanced
5	Expert

<tbody>
</tbody>

Total Score	Talent Multiplier	Purpose Driven	Principled Agility	Results Oriented	Strategic Orientation	Managing Complexity	Collaborative Relationships	Risk Mitigation
16	Needs Development	Basic	Basic	Needs Development	Intermediate	Basic	Needs Development	Advanced

<tbody>
</tbody><colgroup><col><col><col><col><col><col span="4"></colgroup>

- Younes

 

[barry houdini]

Hello Younes, welcome to MrExcel

If text is in range B2:I2 then try this formula to include all the information in the formula

=SUM(COUNTIF(B2:I2,{"Needs Development","Basic","Intermediate","Advanced","Expert"})*{1,2,3,4,5})

.....or if you have that table in Y2:Z6, e.g. Z2:Z6 for text values and Y2:Y6 for numbers and then use this version

=SUMPRODUCT(COUNTIF(B2:I2,$Z$2:$Z$6),$Y$2:$Y$6)​

 

[yyasin]

Barry, Thank you very much! That worked.