Sum a row IF there is a number in the cell

01/13/22

My brain is fried. Can someone help me figure out how to sum a ROW where some of the cells have a number in them and some don't. The rows with the numbers are not sequential, more like every other cell.

Thanks,

Jeana

• Hey @Jeana

ColA ColB ColC ColD ColE Sum Column

1 text1 date 2 3

where ColA is the first column in your SUM range and ColE is your last. The exact number of columns is up to you.

=SUMIFS(ColA@row:ColE@row, ColA@row:ColE@row, ISNUMBER(@cell))

Does this work for you?

Kelly

• Hi Kelly, it's close (no error) but not the right answer. The formula you shared does the following:

example

Column 1 Column 2 Column 3 Column 4 Your formula

4. BLANK. N/A. 3. 4

No matter what I changed in the data it only results in the first value of the first cell referenced in the formula.

• That didn't display very well. Let me try again. It's basically only getting the data in the first cell referenced. I don't get an error.

I'm also thinking about the logic in the following way since the only value in the range that is not a number is N/A but can't get the formula to work across cells.

SUMIF CELL RANGE not equal to N/A

I think it's the syntax around the cell range that's confusing me.

Thanks,

• Hey @Jeana

The data that you showed above - are you using the periods to indicate a column break, or is your data a number followed by a period?

Is it possible for you to provide the formula you used (the one that only provides the first column)?

Kelly

• See, my brain really is fried on this one. I think it's pretty simple but I can't get this formula to work

=SUMIF(ISNUMBER([Q1b Rating-Analytics and asset to project level]1:[Q1c Rating:Partner view of assets]1),())

The row looks like this with these columns repeating. On this row it's possible to have a blank or text (N

• Hey @Jeana

Since I always use the plural version of a function in case I later choose to add more criteria, I'm writing it as a SUMIFs. Also, since you are planning on making this a column formula, you must use @row instead of calling out the row number.

I think my original formula would work for you if it was entered directly. Please try it and let me know.

=SUMIFS([Q1Rating-Analytics and asset to project level]@row:[Q1c Rating:Partner view of assets]@row, [Q1Rating-Analytics and asset to project level]@row:[Q1c Rating:Partner view of assets]@row, ISNUMBER(@cell))

• Hey Kelly,

I'm not clear on why you repeated the range in this formula. I've tried it and it's unparsable when I enter this as well as if I don't repeat the range.

=SUMIF([Q1 Rating-Ingesting assets from multiple sources]@row:[Q11 Rating Support]@row), [Q1 Rating-Ingesting assets from multiple sources]@row:[Q11 Rating Support]@row), ISNUMBER(@cell))

• Hey @Jeana

The format of the SUMIFS is (Sum range, range1, criteria1, range2, criteria2, etc)

Anytime you are unclear about the syntax of a formula, if you open the formula window the help wizard shows you the expected format. If you hover on the different elements within the formula you wrote, you will see the help text highlight in the wizard, indicating where in the formula syntax smartsheet believes you to be at.

You should be able to literally copy paste my formula into your sheet

If, after adding the 's' you still get an error, please provide a screenshot of the formula so that I may see the colored text.

Kelly

• Hey Kelly,

I did add the S for SUMIFS (my bad on typing it in here). The problem was an extra (. Got it now!

Thanks