Sum cells containing numbers and text based on a condition
Question:
I want to sum cells that have a "C" and a decimal number.
The cells have other numbers and variables in them as well, but I only want to add ones
with "C"s.
Example of what cells contain:
7.5 C
H
7.5
9.4 C
3.2 F
HV
V
4.7 C
Answer:
The array formula in the picture above searches for string "C" in cell range C3:C10 and extracts the corresponding number part.
Then it adds
Array formula in cell C11:
To enter an array formula, type the formula in cell B3 then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully.
Don't enter the curly brackets yourself, they appear automatically.
Explaining formula
Use "Evaluate Formula" on tab "Formula" on the ribbon to go through the steps in the formula.
Step 1 - Find values containing text string
The SEARCH function allows you to search for a specific string in a cell range or array.
SEARCH("C", C3:C10)
becomes
SEARCH("C", {"7.5 C";"H";7.5;"9.4 C";"3.2 F";"HV";"V";"4.7 C"})
and returns {5; #VALUE!; #VALUE!; 5;#VALUE!; #VALUE!; #VALUE!; 5}.
The issue with this array is that the SEARCH function returns a #VALUE error if the string is not found.
Number 5 is where "C" is found in the string, in other words "C" is found in position 5 counting from the left.
For example, 7.5 C has five characters, "C" is the fifth character in the string.
Step 2 - Identify numbers in array
The ISNUMBER function handles error values in a great way, instead of returning the error value it converts it to FALSE.
ISNUMBER(SEARCH("C", C3:C10))
becomes
ISNUMBER({5; #VALUE!; #VALUE!; 5;#VALUE!; #VALUE!; #VALUE!; 5})
and returns {TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE}.
We now have an array without error values that is easier to work with.
TRUE indicates that the corresponding cell contains the string "C" and FALSE that "C" is missing.
Step 3 - Remove C from value and convert it to a numerical value.
Since C is the last character we can use the LEFT function to extract a given number of characters.
The LEN function counts the number of characters in each cell. Subtracting with 1 returns how many characters we need to extract counting from left.
LEFT(C3:C10, LEN(C3:C10)-1)*1
becomes
LEFT(C3:C10, {4; 0; 2; 4; 4; 1; 0; 4})*1
becomes
{"7.5 ";"";"7.";"9.4 ";"3.2 ";"H";"";"4.7 "}*1
To convert text values to numerical values I multiply with 1.
{"7.5 "; ""; "7."; "9.4 "; "3.2 "; "H"; ""; "4.7 "}*1
becomes
{7.5; #VALUE!; 7; 9.4; 3.2; #VALUE!; #VALUE!; 4.7}
We can now use this array to replace the boolean array if value is TRUE.
Step 4 - Convert boolean values to corresponding numbers
The IF function allows you to use the boolean array and replace it with numbers (if TRUE) or nothing (if FALSE).
IF(ISNUMBER(SEARCH("C", C3:C10)), LEFT(C3:C10, LEN(C3:C10)-1)*1, "")
becomes
IF({TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE}, {7.5; #VALUE!; 7; 9.4; 3.2; #VALUE!; #VALUE!; 4.7}, "")
and returns {7.5; ""; ""; 9.4; ""; ""; ""; 4.7}.
The error values are ignored and "" (nothing) is returned in those locations.
Step 5 - SUM numerical values ignoring blank values
The SUM function is intelligent, it ignores blank and text values.
SUM(IF(ISNUMBER(SEARCH("C", C3:C10)), LEFT(C3:C10, LEN(C3:C10)-1)*1, ""))
becomes
SUM({7.5; ""; ""; 9.4; ""; ""; ""; 4.7})
and returns 21.6 in cell C11.
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
The easiest way to check if a cell contains a specific text string is, in my opinion, the IF and […]
Count cells containing text from list
The array formula in cell F3 counts cells in column B that contains at least one of the values in […]
Extract unique distinct values if value contains string
The image above demonstrates a formula in cell F3 that extracts unique distinct values from column B if they contain […]
The picture above shows different values in column B and a formula in column C that tries to identifies the […]
The LEFT function allows you to extract a string from a cell with a specific number of characters, however, if […]
The formula in cell C3 grabs the first word in B3 using a blank as the delimiting character. =LEFT(B3,SEARCH(" ",B3)-1) […]
I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]
Convert array formula to a regular formula
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
Find positive and negative amounts that net to zero
I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]
How to use the SEARCH function
Returns a number representing the position a substring is, if found in a textstring. FIND is case sensitive and SEARCH is not.
The easiest way to check if a cell contains a specific text string is, in my opinion, the IF and […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
Sum values between two dates and based on a condition
In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]
Count how many times a string exists in a cell range (case insensitive)
Question: How do I count how many times a word exists in a range of cells? It does not have […]
One Response to “Sum cells containing numbers and text based on a condition”
Leave a Reply to Ashish Mathur
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi,
You may also try this array formula
=SUM(IF((ISNUMBER(SEARCH("C",A1:A8))),TRIM(LEFT(SUBSTITUTE(A1:A8," ",REPT(" ",99)),20))*1))