SUMPRODUCT use but with unknown number of rows

[kobyb]

Hi MrExcel.
I am back after 10 years.
Please have a look at the sample below.
I need to put a value on every line having "file:" in the second column. The value requested is a sum of the numbers in the forth column following this line until the next "file:" line.
I know it is not trivial, but sure it is possible.
Thanks,
Koby Biller


[TABLE="width: 511"]
<tbody>[TR]
[TD="align: right"]57[/TD]
[TD="align: left"]file:[/TD]
[TD="align: left"]HIRES-~1[/TD]
[TD="align: center"]#VALUE![/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]58[/TD]
[TD="align: right"]208[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]59[/TD]
[TD="align: left"]file:[/TD]
[TD="align: left"]-89077~1.CAC[/TD]
[TD="align: center"]#VALUE![/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]60[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]61[/TD]
[TD="align: right"]118[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]62[/TD]
[TD="align: left"]file:[/TD]
[TD="align: left"]-25634~1.CAC[/TD]
[TD="align: center"]#VALUE![/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]63[/TD]
[TD="align: right"]2596[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]64[/TD]
[TD="align: right"]2599[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]65[/TD]
[TD="align: right"]2601[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]66[/TD]
[TD="align: right"]2603[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]67[/TD]
[TD="align: right"]2605[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]68[/TD]
[TD="align: right"]2607[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]69[/TD]
[TD="align: right"]2609[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]70[/TD]
[TD="align: left"]file:[/TD]
[TD="align: left"]-88614~1.CAC[/TD]
[TD="align: center"]#VALUE![/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]71[/TD]
[TD="align: right"]2558[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]72[/TD]
[TD="align: left"]file:[/TD]
[TD="align: left"]838425~1.CAC[/TD]
[TD="align: center"]#VALUE![/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[kobyb]

Thank you for answering my question. I am sorry that I was not too clear.
Both solutions were mistaken while handling the index, and when I had a file name which is a number the solution did not work. I had another problem with both solutions, for my spreadsheet is huge.
In any case, everything is solved now, and I thank you for opening my eyes to see what I believed that can be done.
I'll split the points you deserve.
Koby.

 

[Aladin Akyurek]

Thank you for answering my question. I am sorry that I was not too clear.
Both solutions were mistaken while handling the index, and when I had a file name which is a number the solution did not work. I had another problem with both solutions, for my spreadsheet is huge.
In any case, everything is solved now, and I thank you for opening my eyes to see what I believed that can be done.
I'll split the points you deserve.
Koby.

Not sure what you mean by "mistaken while handling the index"? Care to elaborate and share the solution with us?

 

[kobyb]

The command that did the work is very close to what you have suggested,
The only difference I made was increasing the file size (7272 rows)
start the SUM one line later
Finish one line earlier.

I could bot accomplish it without your support. Thanks.

<code>
=IF(B16="file:",SUM(C17:INDEX(C17:$C$7272,MATCH("file:",B17:$B$7272,0)-1)),"")
</code>

Another hint that I need, for I failed to find: How to close an entry (ig at all) and how to reward my helpers?

 

[Aladin Akyurek]

The command that did the work is very close to what you have suggested,
The only difference I made was increasing the file size (7272 rows)
start the SUM one line later
Finish one line earlier.

I could bot accomplish it without your support. Thanks.

<code>
=IF(B16="file:",SUM(C17:INDEX(C17:$C$7272,MATCH("file:",B17:$B$7272,0)-1)),"")
</code>

Adding the -1 bit is a good idea... Thanks for providing feedback.

Another hint that I need, for I failed to find: How to close an entry (ig at all) and how to reward my helpers?

A simple thanks in a reply is enough.