SUMIF formula with range
=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.
Comments
-
Is there an extra end parenthesis?
-
If I remove the double end parenthesis, it states #CIRCULAR REFERENCE.
-
If I remove the @row, it states #BLOCKED..
-
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.
-
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])
-
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?
-
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.
-
Or if you want to make a copy of the sheet and share it temporarily, it might be easier to see and assist.
-
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.
-
You've been very helpful--I would love to. Who should I share with?
-
You can share to nicolai.larsen@heritagebanknw.com
-
This is tricky. This didn't seem to work.
-
Sorry - I wasn't looking closely enough. Just remove everything but "Q.3.18" after that second comma. You don't need a range.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!