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

### Misc category

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]

This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]

I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]

Have you ever tried to enter an array formula in merged cells? Then you are familiar with this error message: […]

Column B contains document names, many of them are duplicates. The adjacent column C has the revision of the documents […]

This formula decodes a URL-encoded string, replacing specific percentage symbol (%) and a hexadecimal number with non-alphanumeric characters. Excel 365 […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

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