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.

Find more posts tagged with

Construction

Finance

Formulas

Comments

RMRGSA

Is there an extra end parenthesis?

Camper Reparadise

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

Camper Reparadise

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

RMRGSA

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.

Nic Larsen

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

Camper Reparadise

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?

Nic Larsen

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.

Nic Larsen

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

Camper Reparadise

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.

Capture 1.PNG

Capture 2.PNG

Camper Reparadise

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

Nic Larsen

You can share to nicolai.larsen@heritagebanknw.com

Mike Wilday

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

Camper Reparadise

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

Mike Wilday

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

Nic Larsen

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?