## 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.

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.

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.

### Download Excel file through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.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 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

Kamran Mumtaz asked: Is there any way to use VLOOKUP for multiple criteria and I do not want to use CSE?; […]

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

### 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.

**Contact Oscar**

You can contact me through this contact form

Hi,

You may also try this array formula

=SUM(IF((ISNUMBER(SEARCH("C",A1:A8))),TRIM(LEFT(SUBSTITUTE(A1:A8," ",REPT(" ",99)),20))*1))