SumIf if cell is a number

Mallory McFall

Hello! I am trying to create a formula that calculates the sum of the following columns: Column1,Column2, and Column3. These single select dropdown columns are restricted to the options N\A & numbers 0-10. I need to create a formula that omits the option N\A and sums the other columns. Thanks!

Paul Newcome

You are going to need to convert the non-n/a cells into numeric values first.

I would suggest a helper column for each of the columns you want to sum.

=IFERROR(VALUE([Column1]@row), 0)

=IFERROR(VALUE([Column2]@row), 0)

=IFERROR(VALUE([Column3]@row), 0)

Then you can just sum those helper columns together.

Mallory McFall

That is a great suggestion. Thank you so much Paul!

Paul Newcome

Actually... Are they single select dropdowns? If so, you should be able to use

=SUM([Column1]@row:[Column3]@row)

Single select could be outputing numerical values already in which case a basic SUM function should work for you.

Mallory McFall

They are single select dropdown, however the columns are not side by side, therefore, I do not think the use of : will suffice.

Paul Newcome

Ah. In that case you could reference all three columns individually.

=SUM([Column1]:[Column1], [Column2]:[Column2], [Column3]:[Column3])