SumIf if cell is a number
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!
Answers
-
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.
-
That is a great suggestion. Thank you so much Paul!
-
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.
-
They are single select dropdown, however the columns are not side by side, therefore, I do not think the use of : will suffice.
-
Ah. In that case you could reference all three columns individually.
=SUM([Column1]:[Column1], [Column2]:[Column2], [Column3]:[Column3])
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 142 Industry Talk
- 473 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 489 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!