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]

Hi & welcome to MrExcel.
How about

=SUM(--TEXTBEFORE(B6:G6,{"£","!"},,,1))

 

[messiah1028]

Hi & welcome to MrExcel.
How about

=SUM(--TEXTBEFORE(B6:G6,{"£","!"},,,1))

That works perfectly, thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[messiah1028]

Hi again,

Because each row length will be different based on the number of entries I don't want to manually select the range of cells so I have been using a starting column of B and CTRL + Shift + Right arrow to get to the end of the sheet, so essentially my range is B2:XFD2. When I use the formula above it brings back a #value error and I think it is because of the empty cells. Is there anything additional that can be added to the above formula so I can use the range B2:XFD2 and it will ignore the empty cells

Thanks

 

[messiah1028]

And apologies for not mentioning it before I didn't know it would cause an issue

 

[Fluff]

How about

=SUM(--TEXTBEFORE(FILTER(B2:XFD2,B2:XFD2<>"",0),{"£","!"},,,1))

 

[Scott Huish]

Perhaps:

=SUM(--TEXTBEFORE("0"&B2:XFD2&"£!",{"£","!"}))

 

[messiah1028]

I've just tried it on all the sections of my spreadsheet where it's needed and it works like a charm - thank you again, I wish I knew excel that well!

How about

=SUM(--TEXTBEFORE(FILTER(B2:XFD2,B2:XFD2<>"",0),{"£","!"},,,1))

 

[messiah1028]

Perhaps:

=SUM(--TEXTBEFORE("0"&B2:XFD2&"£!",{"£","!"}))

This works too! Thank you