Finding Sum of Numbers Between Rows with a variable number of Rows
I am looking to create a a formula that will find the sum of a certain column, but the complex portion is that I want it to only sum the rows if it is between certain rows. You can see in this example, I only want to sum the number of calls between "Location Recommendation". But the number of rows is going to vary, it might be 2 days between location recommendations or it could be 50. I am guessing this is a Index, Match, Sumif or some combination, but I just can't land the formula in my attempts. Any help is appreciated from the Smartsheet Wizardry. Thanks
Answers
-
Hi @Ross Novotny ,
Can you indent the Daily Activity row? That will allow you to use SUM(Children()) in each location row to sum the daily activity lines.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
@Mark Cronk that's a good thought. But these entries are coming from an input form. The person inputting the information will not really be viewing or manipulating the grid unfortunately. Unless there is a way to default a term "Daily Activity" to be indented.
-
Ross,
Where do you want this formula to go? You would probably need to create a separate column for this.
If this is your intention then, Add the following columns:
- "LINE-ID" : Auto Number Column
- "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)
- "TOTAL CALLS" : Column Formula: =IF([Activity Type]@row = "Location Recommendation", SUMIFS([Consumer Calls]:[Consumer Calls], [ROW#]:[ROW#], >[ROW#]@row, [ROW#]:[ROW#], <MIN(COLLECT([ROW#]:[ROW#], [Activity Type]:[Activity Type],"Location Recommendation", [ROW#]:[ROW#], >[ROW#]@row))))
Help Article Resources
Categories
Check out the Formula Handbook template!
