How to sum a row of data with some cells containing an extra character

[messiah1028]

Hi everyone,

I’m having an issue summing a row of cells, some of which contain a character that is not a letter. I need this character to differentiate some of the entries from one another. Some of the cells have a number followed by ‘!’ or ‘£’ however I may need to add an additional character at some point.

When I sum the length of the row I receive a total that is only the cells without an extra character but I need the cell containing the extra character to be counted.

For info, the numbers in front of the ‘!’ or ‘£’ could be in the ones, tens, hundreds or possibly thousands.

An example of a row is:

Can anyone suggest a formula that would fix my problem?

I initially tried this formula from a thread with a similar issue: =SUM(SUBSTITUTE(C4:C8, "*",” ”) but this returned an output of 0 unless I'm using it incorrectly.

A way around this would be to differentiate the cells another way, I initially tried doing this by filling the cell with a specific colour and using a custom function to count the cells by colour but this seemed to slow down my computer and it didn’t update as I inputted data and filled the cells so it wasn’t ideal.

If anyone thinks of an easier way of differentiating the cells that can then be used in further formulas and is easier than the summing conundrum above feel free to suggest it! Ideally I would like to keep the values with the extra character in the same row as the other cells as the order is quite important to other calculations I'm doing.

Thanks in advance

 

[Fluff]

Glad we could help & thanks for the feedback.