Finding Sum of Numbers Between Rows with a variable number of Rows

Ross Novotny

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

Mark Cronk

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

Ross Novotny

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

Leibel S

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:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)
3. "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))))