Summing a Column Containing Text

[adamzee]

Hi,
I have a table which looks similar to below. My question is I would like to create a calculated field which sums the question "How many items of POS", but due to the other responses containing text I am having difficulty doing this. Any help would be appreciated.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Shop[/TD]
[TD]Question[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]Who did you speak to?[/TD]
[TD]Manager[/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]Was point of sale displayed?[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]How many items of POS?[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]Who did you speak to?[/TD]
[TD]Assistant Manager[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]Was point of sale displayed?[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]How many items of POS?[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]Who did you speak to?[/TD]
[TD]Manager[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]Was point of sale displayed?[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]How many items of POS?[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]Who did you speak to?[/TD]
[TD]Manager[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]Was point of sale displayed?[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]How many items of POS?[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

So if the result of Sum in your sample would be 20.
Use this formula:
Put this formula in say A1
=SUMIFS(C1:C10,B1:B10,E1)
Put *Pos* in cell E1
Your result will be in A1

 

[Ozeroth]

If you're wanting a PowerPivot measure to convert text to numbers and sum, this is a starting point.

=
SUMX (
    'Table',
    IFERROR ( VALUE ( 'Table'[Result] ), BLANK () )
)

 

[My Aswer Is This]

I'm sorry I'm commenting in wrong forum

So if the result of Sum in your sample would be 20.
Use this formula:
Put this formula in say A1
=SUMIFS(C1:C10,B1:B10,E1)
Put *Pos* in cell E1
Your result will be in A1

 

[adamzee]

If you're wanting a PowerPivot measure to convert text to numbers and sum, this is a starting point.

=
SUMX (
    'Table',
    IFERROR ( VALUE ( 'Table'[Result] ), BLANK () )
)

Thanks heaps Ozeroth, that works perfectly