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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!