# Calculating sum of all columns without text

• ### Question

• I have a query where two columns have text values and other columns have numbers. I would like to sum up only the columns with numbers (excluding the two columns with text).

This code gives me an error as it sums all columns (including the ones with text):

How can I modify this to exclude the columns with text values?

Thanks a lot!

Aish

• Edited by Friday, June 30, 2017 12:11 AM
Friday, June 30, 2017 12:08 AM

• Based on your formula, it appears that you are summing the row, and not the column. If this assumption is true, you can modify your step as follows:

`AddedCustom = Table.AddColumn(ChangedType, "SumRow", each List.Sum(List.Select(Record.ToList(_), each _ is number)))`

However, unless you have a lot of numeric columns, this formula is overkill, since it requires three function calls.

If you are not summing values from many columns, I would suggest an alternative that uses just one function call. Assuming that you have three numeric columns named Number1, Number2, and Number3, then

`AddedCustom = Table.AddColumn(ChangedType, "SumRow", each List.Sum([Number1], [Number2], [Number3]))`

• Edited by Friday, June 30, 2017 2:18 AM
• Marked as answer by Friday, June 30, 2017 6:06 PM
Friday, June 30, 2017 2:14 AM

### All replies

• Based on your formula, it appears that you are summing the row, and not the column. If this assumption is true, you can modify your step as follows:

`AddedCustom = Table.AddColumn(ChangedType, "SumRow", each List.Sum(List.Select(Record.ToList(_), each _ is number)))`

However, unless you have a lot of numeric columns, this formula is overkill, since it requires three function calls.

If you are not summing values from many columns, I would suggest an alternative that uses just one function call. Assuming that you have three numeric columns named Number1, Number2, and Number3, then

`AddedCustom = Table.AddColumn(ChangedType, "SumRow", each List.Sum([Number1], [Number2], [Number3]))`

• Edited by Friday, June 30, 2017 2:18 AM
• Marked as answer by Friday, June 30, 2017 6:06 PM
Friday, June 30, 2017 2:14 AM
• Hi Colin,

Thanks a lot , it worked! I had to add up values from several columns. So used the first option you mentioned, got the results I wanted.

Thanks again!

Aish

Friday, June 30, 2017 6:08 PM
• Hi Aish,

if you have got text in columns whose names are known you can use a slightly different solution ( i did not test performance difference)

```let
Rec = [C = null, E = null],
Source = Table.FromRows({{4,2,"Colin",5,"Banfield",15}, {1,7,"Bill",6,"Szysz",12}}, {"A","B","C","D","E","F"}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type number}, {"B", type number}, {"C", type text}, {"D", type number}, {"E", type text}, {"F", type number}}),