Sum A Range Based on The Difference Between Text In Another Column

[Barrett Snyder]

I apologize ahead of time for a confusing title but it's the best way I could think of to describe it.

I am putting together a sheet that converts a standard report from our fleet management software to a standard payroll format. In doing so I need to develop individual entries for each trip between pre-mapped zones, however the software breaks up stops along the way into their own row entries. 

I am looking to sum all row entries between the pre-defined zones. For example, the first entry would sum the driving durations listed in F9:F16 in cell J16 and the idling durations listed in G9:G16 into cell K16. 

This summation needs to occur between rows with text listed in column I. 

Therefore, the second entry would sum F17:F18 into cell J18 and sum G17:G18 into cell K18.

The third entry would sum F19:F19 into cell J19 and sum G19:G19 in cell K19.

And so on...

What I'm trying to capture is that total drive time to a new specified location and the time spent at the specified location in one entry. I have been struggling to come up with a way to do this referencing the changing text in column I which denotes arriving at a pre-mapped zone.

If anyone has an idea of a standard formula I can place into columns J and K and drag down that would only place the entries like I specified above and leave the rest blank that would be greatly appreciated. If this is not possible without using VBA of some kind let me know and I will just drop the project, its definitely more of a "it would be nice if we could" type of deal. 

Thanks,

[#2 · Accepted solution]

I think I can do this with one extra column to calculate the number of blanks cells above whatever zone you are up to..

Try this. In K9 enter this array formula:

=ROW()-1-MAX(IF(NOT(ISBLANK($I$1:I8)),ROW($I$1:I8)))

you need to enter it with ctrl+shift+enter.

Next in K9 add this formula:

=IF(ISBLANK(I9),"",SUM(OFFSET(F9,0,0,(J9+1)*-1)))

copy both down however far you have data..

That should sum everything in column F between the previous Zone and this one..

[#3]

Thanks for your reply Phillip,

I am unfamiliar with the offset formula so I appreciate the idea.

Not sure I'm quite clear on the execution of what you suggested, however, I understand the logic. 

Any chance you could look back through what you wrote and check how it applies to what I have pictured above? Tried getting both formulas into K9 just to be sure, but I'm pretty sure that was just a typo.

I believe I would need to insert an additional column, logically creating a new column "J" for the array function to work in, and shifting my previous columns "J" and "K" to "K" and "L." From here your second formula would be used in both column K to reference the Driving Duration in column E and in column L to reference Idling Duration in column F.

If that sounds about right, I'd really appreciate it if you would just update what you had. Excited to be on the path to a relatively easy solution. 

[#4]

Disregard, both formulas work perfectly once setup how I suggested in my first reply.

My issue was that what I thought were blanks in column I were actually reference formulas that auto-populated when the report was downloaded off of the software. Therefore the array formula wasn't working as it should. I just came up with a way to get those removed so I am up and running.

Thank you for the help,

-Barrett

[#5]

Super!

Glad it worked. It was quite a fun puzzle.