22

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

Functionality
Industry

Comments

Is there an extra end parenthesis?

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

In reply to by Nic Larsen

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?

In reply to by Camper Reparadise

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. 

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

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

 

In reply to by Mike Wilday

I'm sorry---not sure I follow that.

The function of this formula is a bit puzzling to me as well, especially the last halfl:

=IF(COUNT(CHILDREN([Sales Tax Sales]@row)) > 0, COUNT(ANCESTORS()) + 1)

In reply to by Camper Reparadise

Its saying if the count of any children (indented rows) is greater than 0, then count all the ancestors and add a 1. Not sure what that's doing in your case. but that's what it is doing. Ancestors would be all the parent rows (I think). 

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. 

In reply to by Nic Larsen

The sums look right. And it's so good to see it work. Yes, that's what I was worried about; the formula isn't so useful if it can't operate without adjusting the range continually..This has given me a good sense of what will work. I'll keep at it. Very much appreciate your work and insight! 

Sorry... let me type it up for you.

I found another issue too... If you look at how the formula should be laid out, it should be SUM Range first, Criterion Range 2nd, and the Criterion 3.

As long as I retyped those column names right, this should work for you.  

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

In reply to by Mike Wilday

Column names are right, but unforntunately, #INCORRECT ARGUMENT.

I'm clocking out of this issue for a bit. Thank you for your help. I'll update this thread when I figure out best fit. 

 

 

=SUMIFS([Payments in Quarter]1:[Payments in Quarter]34, [Total Sales of Goods and Services]1:[Total Sales of Goods and Services]34, "Q.3.18")

Sorry - I set it up as a SUMIFS, but didn't specify that in the formula. Try this one. 

Did this version work for you?