#HOW TO SUM A COLUMN IN EXCEL WITH CERTAIN NUMBER HOW TO#
Hope you have learned how to sum a column where numbers are mixed with text in Google Sheets. For example, you can compare values in two cells, calculate the sum or product of cells, and so on. It’s one of the finest formulae in data manipulation in Google Sheets. I recommend you to always keep this formula with you or bookmark this page. It works in a column, row and even in an array (multiple columns). To understand it better refer the Formula # 1 (single column).įormula # 3 is a dynamic formula to sum numbers with text in Google Sheets. To check your work, click the cell with the sum and look at the formula bar atop the screen. See how I have used the TEXJOIN inside the formula. You can sum numbers or cells by creating a sum formula manually. To sum alphanumeric characters (cells with numbers and text) in multiple columns again you should tweak the formula.įormula # 3: =ArrayFormula(sum(IFERROR(split(textjoin("|",true,REGEXREPLACE(B2:F3&"", "+", "|")),"|")))) Sum the values based on another column if only is certain text. Sum Alphanumeric Characters in Multiple Columns In Google Sheets Here Use the TRANSPOSE to transpose the row to column. If the numbers with text (alphanumeric characters) are in C2:G2 instead of C2:G2&"" use TRANSPOSE(C2:G2)&"".įormula # 2: =ArrayFormula(SUM(IFERROR(SPLIT(REGEXREPLACE(TRANSPOSE(C2:G2)&"", "+", "|"), "|")))) But you need to tweak the formula a little bit. 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. Sum Alphanumeric Characters in a Row in Google Sheets You only need to modify the cell references in this formula to work. That means this formula can sum an entire column that contains alphanumeric characters. You can use my formula in any column as it’s an array formula. So it can be useful to clean your Google Sheets Data.
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.
=ArrayFormula(IFERROR(SPLIT(REGEXREPLACE(C2:C&"", "+", "|"), "|")))įinally, the SUM function sums all the values. The Iferror is to remove any other errors associated with blank cells in the column. I want a separator in between like 350 | 450 so that I can split it into two separate numbers. In such a case, if you use "" instead of |, Regex will consider this as one whole number like 350450. I opted to go with the Pipe symbol as sometimes any of the cells may contain multiple numbers as below. =ArrayFormula(SPLIT(REGEXREPLACE(C2:C&"", "+", "|"), "|"))Īctually, the Split function is not required if you use "" instead of the Pipe symbol in Regexreplace.