# SUMIF formula with range

Options
edited 12/09/19

=SUMIF([Total Sales of Goods and Services]@row :[Total Sales of Goods and Services]@row, [Payments in Quarter]@row = "Q.3.18"))

The purpose of this formula is to sum all totals of Total Sales of Good and Services if the Payments were made in Quarter 3, 2018; I'd like to make this formula workable per selected quarter for reporting purposes. So far the results are #UNPARSEABLE.

Tags:
«1

• Options

Is there an extra end parenthesis?

• Options

If I remove the double end parenthesis, it states #CIRCULAR REFERENCE.

• Options

If I remove the @row, it states #BLOCKED..

• edited 02/15/19
Options

What column are you entering the formula in?

Your original formula minus the extra parenthesis at the end worked for me so I think you're close.

• ✭✭✭✭✭✭
edited 02/15/19
Options

Drop the @rows from your range

=SUMIF([Total Sales of Goods and Services]:[Total Sales of Goods and Services], [Payments in Quarter]@row = "Q.3.18")

This was missing a piece. This will work:

=SUMIF([Payments in Quarter]:[Payments in Quarter], [Payments in Quarter]@row = "Q.1.19", [Total Sales of Goods and Services]:[Total Sales of Goods and Services])

• Options

I putting in the formula in the Totals Sales of Goods and Services column and it says #BLOCKED...I've tried in the Payments in Quarter as well with the same result.

Could it be disturbed by hiearchy? I am putting the formula at the very bottom of the sheet in a second, separate parent hierarchy. Under the first parent hierarchy, I tally the sums, while quarters are applied under 2nd parent's child. Does that make sense?

• ✭✭✭✭✭✭
edited 02/15/19
Options

You'll need to define the range if you are doing that since the way I wrote it it is looking at the entire column. Just add the # on each side of the range so its not counting the box you are placing the formula. That should fix it. Or move the formula to a new column.

*edit. I just re-read your statement. I think I know what you are doing but if you have child rows rolling up to Parent rows, it may not calculate correctly the total. You may need to add a 3rd criteria to exclude parent or include on child rows.

• ✭✭✭✭✭✭
Options

Or if you want to make a copy of the sheet and share it temporarily, it might be easier to see and assist.

• Options

Ah, I see..as a test, I limited the range and found that it included the sum with quarterly criteria, but also carried it up the to a row it didn't apply to.

• Options

You've been very helpful--I would love to. Who should I share with?

• ✭✭✭✭✭✭
Options

You can share to nicolai.larsen@heritagebanknw.com

• ✭✭✭✭✭✭
Options

Try changing the @row in your criteria section to @cell... so it will look at each individual cell in the range.

• Options

This is tricky. This didn't seem to work.

• ✭✭✭✭✭✭
Options

Sorry - I wasn't looking closely enough. Just remove everything but "Q.3.18" after that second comma. You don't need a range.

• ✭✭✭✭✭✭
Options

Take a look. What i don't like is that I can't use an endless range which means you'll always have to edit the formula. If you can move whatever is causing it to #Blocked, then it'll be easier to manage.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!