how to add numbers in google sheets

Google Sheets considers numbers mixed with text as text only. So normally you can not Sum cells with numbers and text in a column in Google Sheets.

If your data entry operator is new in his job, you may surely get a chance to experiment with your data manipulation prowess 🙂

An inexperienced DEO can cause the following common error in your Spreadsheet.

A common problem you may find in your Spreadsheet is a column/row or an array that has numbers mixed with text (alphanumeric characters).

Normally you can see such entries in columns with currency symbols as well as units of measurements.

Summing Cells Contain Numbers and Text in a Column Returns Error

Can I use the Sum function to sum the column C in the following sample data set?

Nope! You can't use the SUM function to sum a mixed content column as below.

          =sum(C2:C6)

Example to sum cells with numbers and text in Sheets

The above formula will return 0 as there is no numeric value in column C. In column C, all the numbers are mixed with units of measurement which are string/text.

In Google Sheets, to deal with such mixed content columns in calculations, you should first remove the texts from numbers.

You can see how to remove texts from numbers in a column and sum in Google Sheets. It's not a complicated thing.

How to Sum Cells With Numbers and Text in a Column in Google Sheets

Here is the formula to sum a column with mixed content in cells. Here I am using the Google Sheets functions like Sum, IFERROR, ArrayFormula, SPLIT, and REGEXREPLACE.

The Formula to Sum an Alphanumeric Column in Sheets

Formula # 1:

          =ArrayFormula(SUM(IFERROR(SPLIT(REGEXREPLACE(C2:C&"", "[^\d\.]+", "|"), "|"))))

Formula Explanation:

We are dealing with an array/range. So we must use the ArrayFormula with other non-array functions in this formula. Now let's start with the formula explanation step-by-step.

The Regexreplace replaces all the texts in column C with the pipe "|" symbols.

          =ArrayFormula(REGEXREPLACE(C2:C&"", "[^\d\.]+", "|"))

Do you know why I have used C2:C&"" instead of C2:C?

This is to convert pure numbers within the range as text strings. Otherwise, if numbers are there in column C, the formula would return VALUE! error as the function Regex expects text values in parameter 1.

VALUE error in Regex in mixed and alphanumeric column data

The Split function splits the above result based on the "|" as the delimiter.

          =ArrayFormula(SPLIT(REGEXREPLACE(C2:C&"", "[^\d\.]+", "|"), "|"))

Actually, the Split function is not required if you use "" instead of the Pipe symbol in Regexreplace.

I opted to go with the Pipe symbol as sometimes any of the cells may contain multiple numbers as below.

350 MT, 450 MT

In such a case, if you use "" instead of |, Regex will consider this as one whole number like 350450. I want a separator in between like 350 | 450 so that I can split it into two separate numbers.

The Iferror is to remove any other errors associated with blank cells in the column.

          =ArrayFormula(IFERROR(SPLIT(REGEXREPLACE(C2:C&"", "[^\d\.]+", "|"), "|")))

Regex formula to extract numbers from column

Finally, the SUM function sums all the values.

That means (as per the above image), if you remove the Sum function from the beginning of the formula, you can use it to extract only numbers from the column after removing text. So it can be useful to clean your Google Sheets Data.

You can use my formula in any column as it's an array formula. That means this formula can sum an entire column that contains alphanumeric characters. You only need to modify the cell references in this formula to work.

Sum Alphanumeric Characters in a Row in Google Sheets

Can I use the above formula in a row?

Why not? With my formula, you can not only sum cells with numbers and text in a column but also sum cells with numbers and text in a row in Google Sheets. But you need to tweak the formula a little bit.

If the numbers with text (alphanumeric characters) are in C2:G2 instead of C2:G2&"" use TRANSPOSE(C2:G2)&"".

Formula # 2:

          =ArrayFormula(SUM(IFERROR(SPLIT(REGEXREPLACE(TRANSPOSE(C2:G2)&"", "[^\d\.]+", "|"), "|"))))

Here Use the TRANSPOSE to transpose the row to column.

Sum Alphanumeric Characters in Multiple Columns In Google Sheets

To sum alphanumeric characters (cells with numbers and text) in multiple columns again you should tweak the formula.

Formula # 3:

          =ArrayFormula(sum(IFERROR(split(textjoin("|",true,REGEXREPLACE(B2:F3&"", "[^\d\.]+", "|")),"|"))))

Sum Cells With Numbers and Text in Multiple Columns

See how I have used the TEXJOIN inside the formula. To understand it better refer the Formula # 1 (single column).

Formula # 3 is a dynamic formula to sum numbers with text in Google Sheets. It works in a column, row and even in an array (multiple columns).

I recommend you to always keep this formula with you or bookmark this page. It's one of the finest formulae in data manipulation in Google Sheets.

Hope you have learned how to sum a column where numbers are mixed with text in Google Sheets.