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.

[TABLE="class: outer_border, width: 200"]
<tbody>[TR]
[TD]1[/TD]
[TD]Needs Development[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Basic[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Intermediate[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Advanced[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Expert[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 900"]
<tbody>[TR]
[TD]Total Score[/TD]
[TD]Talent
Multiplier[/TD]
[TD]Purpose
Driven[/TD]
[TD]Principled
Agility[/TD]
[TD]Results
Oriented[/TD]
[TD]Strategic
Orientation[/TD]
[TD]Managing
Complexity[/TD]
[TD]Collaborative
Relationships[/TD]
[TD]Risk
Mitigation[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Needs Development[/TD]
[TD]Basic[/TD]
[TD]Basic[/TD]
[TD]Needs Development[/TD]
[TD]Intermediate[/TD]
[TD]Basic[/TD]
[TD]Needs Development[/TD]
[TD]Advanced[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col span="4"></colgroup>[/TABLE]


- 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.