Excel Sum numbers in cells that also contain text

S

Simont485

Joined
Feb 23, 2018
Messages
67
Reaction score
24
I have a speadsheet that uses text and numbers in the same cell but I need to sum the numbers only.

I only want to sum the cells that contain the letter T or t. I can get T+8 next to T-6 to give the result a 2.

But I need the same result from D-t+8 next to E-t-2 or any combination of containing T or t

Many thanks in advance
 
Will the numbers always be single digits (ie less than 10)? If so, you can extract the number from each cell using this formula:

=IF(ISNUMBER(SEARCH("t",A1)),VALUE(RIGHT(A1,2)),0)

Then you just need to sum the numbers extracted. I have used SEARCH instead of FIND because it is not case sensitive, and instead of specifying the letter you could link it to a cell instead if you'd prefer.

However this will not work if the number has more than one digit, so I'll need to rethink how to do it if that's the case.
 
Becky said:
Will the numbers always be single digits (ie less than 10)? If so, you can extract the number from each cell using this formula:

=IF(ISNUMBER(SEARCH("t",A1)),VALUE(RIGHT(A1,2)),0)

Then you just need to sum the numbers extracted. I have used SEARCH instead of FIND because it is not case sensitive, and instead of specifying the letter you could link it to a cell instead if you'd prefer.

However this will not work if the number has more than one digit, so I'll need to rethink how to do it if that's the case.
Click to expand...

Thank you but yes the number could be 2 digits and also less than 1 as well.

For sum the number when T is the only text in a range, I have used:

=SUM(IF(LEFT(C4:E4)="T",--RIGHT(C4:E4,LEN(C4:E4)-1))) then ctrl/shift/enter
 
When T/t appears in a cell, is it always the last letter before the number? Or could there be something like T-e+3?
 
Ah ok, so my formula could be amended to this:

=IF(ISNUMBER(SEARCH("t",A1)),VALUE(RIGHT(A1,LEN(A1)-SEARCH("t",A1))),0)

This methodology can be applied to your existing array formula as follows:

=SUM(IF(ISNUMBER(SEARCH("t",C4:E4)),--RIGHT(C4:E4,LEN(C4:E4)-SEARCH("t",C4:E4)),0))

Just make sure you hit Ctrl + Shift + Enter otherwise it won't work as an array.

Hope this helps! :)
 
Becky said:
Ah ok, so my formula could be amended to this:

=IF(ISNUMBER(SEARCH("t",A1)),VALUE(RIGHT(A1,LEN(A1)-SEARCH("t",A1))),0)

This methodology can be applied to your existing array formula as follows:

=SUM(IF(ISNUMBER(SEARCH("t",C4:E4)),--RIGHT(C4:E4,LEN(C4:E4)-SEARCH("t",C4:E4)),0))

Just make sure you hit Ctrl + Shift + Enter otherwise it won't work as an array.

Hope this helps! :)
Click to expand...


That is amazing thank you!!

=SUM(IF(ISNUMBER(SEARCH("t",C4:E4)),--RIGHT(C4:E4,LEN(C4:E4)-SEARCH("t",C4:E4)),0)) works perfectly!

Basically as long as a cell contains T-2 or T+2 or any number, any text to the left is not relevant....just what I wanted :-)
 
OK one problem solved another arises.

So I also have T-e which has its own separate value and is summed elsewhere. But T-e is not recognised by the SUM(IF(ISNUMBER formula and throws an #VALUE error!!

I now need it to ignore T followed by a Letter.

Is this possible?
 
Oh no, how annoying! Is that the only exception or are there others?
 
Becky said:
Oh no, how annoying! Is that the only exception or are there others?
Click to expand...
There are other a lot of others, about 15 possible letters that could follow it. I have a work around that just means not using T in those cases but it would be preferable not to.

I was using SUM(COUNTIF(range{"T-e", "T-m"......})*{7,8,.....}) for the other combinations I have
 
Last edited:
Well this isn't exactly elegant, but it's the only way I can think to fix it...

=SUM(IFERROR(IF(ISNUMBER(SEARCH("t",C4:E4)),--RIGHT(C4:E4,LEN(C4:E4)-SEARCH("t",C4:E4)),0),0))

Basically it will count any errors as 0.

Let me know if it works!
 
Elegant or not it works!

Magnificent!

I wont get a load of emails complaining that I changed a T to an H:)

Thank you so much!
 
I'm glad to hear it! No trouble at all, you're very welcome :)
 
I have used this to sum hours booked as toil "t"
=SUM(IFERROR(IF(ISNUMBER(SEARCH("t",C4:E4)),--RIGHT(C4:E4,LEN(C4:E4)-SEARCH("t",C4:E4)),0),0))

I have used the same equation for holiday "h". (replaced t with h) It works a treat!

But it doesn't work where a cell has H-4/T-2 this happens when someone takes holiday and toil to take a day off.

It is possible to get the numbers associated to H and T out?
 
Just so I understand, you are looking for H, but T might also appear? So in the above example the answer would be -6?
 
Not quite. Id need to sum them separately.

Other cells would contain H-number or T-number only but not usually both. I sum all the H-number cells and T-number cells separately

It is just on occasion I have one cell that contains both H and T followed by a number
 
I love a good problem to solve :D

The reason it's not being picked up is that the formula is designed to pick up the number from the end of the cell that contains the relevant letter. So if we wanted to change it to pick up the number from the middle of the cell then we'll have to change how it works. We could use FIND to see at which point in the string the letter is, and then we could use MID to extract the substring (ie the number)... however, we won't know the length of that the number, which complicates things. You said before that the numbers can be 2 digits, is that right? Or is it the case that cells which feature T and H will only feature single digits?
 
Basically T or H could appear anywhere in a cell...followed by a dash with any number afther them. SO one cell could contain D-e-H-2.5-T-4.5 or D-e-T-4.5-H-2.5 or pretty much any order there of.

I need to get the numbers for T and H

The numbers could be upto 3 digits such as 10.5 (or is that 4?)

I feel it would be easier to change the spreadsheet to allow for text and numbers to be separated but for now that cant happen
 
Last edited:
Oh crikey! :eek: So the numbers that appear in the middle of the cell would be bounded by the letter on one side and a dash on the other? Would it ever be a different sign?

Simont485 said:
I feel it would be easier to change the spreadsheet to allow for text and numbers to be separated but for now that cant happen
Click to expand...

Yup, it would all be far easier if the data could be entered in different cells. Why is it that some people are so averse to change?! I bet once they got used to it they would find it easy! :rolleyes:
 
Back
Top