Sum total of cells containing specific text

[rob1987]

Hello All,

I have a spreadsheet where the cells in a row contain either a number or text and a number. I use the following formula to sum the total of any cell which contains both text and a number:

=SUM(IF(b26:ac26<>"",IF(NOT(ISNUMBER(--LEFT(b26:ac26,1))),--MID(b26:ac26,2,256))))

Can anyone please advise on how I can change this so that I can get it to sum only the cells that contain a specific text, e.g. anything that contains 'M' so just the cells that say M15, M20, M21, etc.

Hope someboday can help - it would be very much appreciated.

Thank you

 

[rob1987]

I managed to work out a formula in the end, if anyone who reads this is interested or has a similar problem I got the following to work, as an array formula (Ctrl, Shirt + F2):

=SUM(IF(LEFT(B25:AF25)="M",SUBSTITUTE(0&B25:AF25,"M","")+0))
Change the letter and row as needed and should work.

 

[Robert Mika]

(Ctrl, Shirt + F2)

??

Maybe you are using different version of Excel but normaly array formula is being confirmed by CTRL+SHIFT+ENTER

 

[rob1987]

Yeah - I'd just come back on to correct that, don't know where I plucked F2 from!