Dynamic Offset by detecting blank cells in between values

Hi everyone,

I receive a report that is already formatted like a pivot table and need to create a sum value of cells based relative to the cell with the value's position.

The formula I came up with was: =sum(offset(D2,1,14,[dynamic height],1))

The names of individuals are in column D. The sum of the values are in column R.
The number of blanks in between the values of column D varies because of the data that resides in columns F-P.
See attached data sample.


Column R's formula is =if(isblank(F4),"",min(N4,H4) because I'm trying to find out of the 2 values, which one is the lowest.

What I'm trying to do is get a sum of values in column R that are in between Person 1 and Person 2, then Person 2 and Person 3, etc. The only reference I could think of was the blank cells in between each value to determine the height of the table sum.

I hope that makes sense and really appreciate the time any of you spend in figuring this out!

With last row is row #101
Put the formula from row #100, then copy up to row #2

In cell S100:

Please Login or Register  to view this content.

Copy up to S2

Hi mart.razal and welcome to the forum,

It looks like you need to see the "Fill Down" trick. See it in this video:
https://www.youtube.com/watch?v=5a9gGhRvflw

I think you should also get rid of the merged cells in columns D and E. After you have that you can do a pivot or other trick to finish your problem.
You need to delete column E for the above fill down to work on column D.