SUMIF formula with range

Camper Reparadise

=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. 

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. 

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.