Summing cells with text and numbers

[Bhugz]

Hi all,

I do my indoor cricket team stats and I am having some trouble figuring out what formula to use in order to be able to sum a row of numbers where some cells may contain text and numbers or just text.

Please see the attached spreadsheet to understand what I'm talking about.

In the batting tab, when the batter gets out, they lose 5 runs. Outs are noted by St, R, B or C. In terms of runs, the scorers put wides and noballs to the batter on strike which are noted by W or N and this is worth 2 runs. Sometimes the batter on strike will get runs off a W or N so it's noted down as W+2 (which would give 4 runs) etc. So what I need to be able to do in the batting sheet is sum the total runs and count the number of outs.

In the bowling sheet, it's the same story as the batting tab, but I also need to be able to sum the number of extras given and count the number of wickets taken.

Is anyone able to help me with this?

https://www.dropbox.com/s/t5e9x4stp6kgohm/Sample Stats Autumn 2014.xlsx - link to spreadsheet.


Thanks
Bhagyesh

 

[BabyTiger]

Hi,

While you can develop complex solutions to convert the text to number etc. My advice would be, just keep your solution simple.

In terms of runs, you can enter then exact number of runs in the cell, and add a comment against the cell of what that number represent. For example, in the cell where you have W+2, enter 4, and add a comment as W+2.

I prefer simplistic solutions, but that's me.

 

[Bhugz]

Thanks for that suggestion but as I send the stats out to the team I don't want to have to show comment boxes all over the screen.

 

[Bhugz]

Hi again,

Does anyone have a solution that I could use for my query?


Thanks
Bhugs