Sum a row IF there is a number in the cell

Jeana

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

Jeana

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

Kelly Moore

Hey @Jeana

From your post, I picture your data being arranged similar to

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

Jeana

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.

Appreciate any help you can provide!

Jeana

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,

Kelly Moore

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

Jeana

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

Kelly Moore

Hey @Jeana

Your syntax is incorrect.

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))

Jeana

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))

Kelly Moore

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 didn't insert the sumifS PLURAL Please add the s.

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

https://help.smartsheet.com/function/sumifs

Jeana

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