Forum Discussion

Barrett Snyder's avatar
Barrett Snyder
Copper Contributor
Aug 09, 2018
Solved

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

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 soft...
excel
Formulas and Functions
  • Philip West's avatar
    Philip West
    Aug 09, 2018

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

Philip West's avatar
Philip West
Iron Contributor
Aug 09, 2018

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

  • Barrett Snyder's avatar
    Barrett Snyder
    Copper Contributor
    Aug 09, 2018

    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

    • Philip West's avatar
      Philip West
      Iron Contributor
      Aug 09, 2018

      Super!

      Glad it worked. It was quite a fun puzzle.

  • Barrett Snyder's avatar
    Barrett Snyder
    Copper Contributor
    Aug 09, 2018

    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. 

Resources