adding values with text
Is there any way to sum up cells if they contain text and letters. For
example I would like to sum up 6cs and 12cs for a total of 18. Is there a function where I can make an If statement that would have a condition such as endswith "cs"? Thanks |
you could possibly do this:
=SUM(SUBSTITUTE(A1,"cs",""),SUBSTITUTE(A2,"cs","") ) or even array entered (Ctrl+Shift+Enter): =SUM(IF(ISNUMBER(--SUBSTITUTE(A1:A10,"cs","")),--SUBSTITUTE(A1:A10,"cs",""))) regards, KL "Ron" wrote in message ... Is there any way to sum up cells if they contain text and letters. For example I would like to sum up 6cs and 12cs for a total of 18. Is there a function where I can make an If statement that would have a condition such as endswith "cs"? Thanks |
Do you need the "CS" in the cell as part of the data? You may be able to
use a custom format to add the "CS" and have the cell itself just have numbers. "KL" wrote in message ... you could possibly do this: =SUM(SUBSTITUTE(A1,"cs",""),SUBSTITUTE(A2,"cs","") ) or even array entered (Ctrl+Shift+Enter): =SUM(IF(ISNUMBER(--SUBSTITUTE(A1:A10,"cs","")),--SUBSTITUTE(A1:A10,"cs",""))) regards, KL "Ron" wrote in message ... Is there any way to sum up cells if they contain text and letters. For example I would like to sum up 6cs and 12cs for a total of 18. Is there a function where I can make an If statement that would have a condition such as endswith "cs"? Thanks |
All times are GMT +1. The time now is 08:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com