SUMIF formula with range

2»

Comments

  • 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 Wilday
    Mike 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 Wilday
    Mike 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 Wilday
    Mike 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 Wilday
    Mike Wilday ✭✭✭✭✭✭

    Did this version work for you? 

     

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!