SUMIF formula with range

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

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

  • RMRGSARMRGSA
    edited 02/15/19

    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

    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?

  • edited 02/15/19

    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. 

    Capture 1.PNG

    Capture 2.PNG

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

     

  • You can share to [email protected]

  • Mike WildayMike Wilday ✭✭✭✭✭

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

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

  • Mike WildayMike 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. 

     

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

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

  • Mike WildayMike Wilday ✭✭✭✭✭

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

  • 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! 

  • Mike WildayMike Wilday ✭✭✭✭✭

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

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

     

     

  • Mike WildayMike Wilday ✭✭✭✭✭

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

  • Mike WildayMike Wilday ✭✭✭✭✭

    Did this version work for you? 

     

Sign In or Register to comment.