SUMPRODUCT use but with unknown number of rows

K

kobyb

New Member
Joined
Sep 15, 2012
Messages
6
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]
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Hi,

an attempt

Code: 
=SUM(IF(ISNUMBER(D1:D10000),(A1:A10000="file:")*D1:D10000))

Array to be confirmed with control+shift+enter

Hope that helps
 
Upvote 0
Canapone said:
Hi,

an attempt

Code: 
=SUM(IF(ISNUMBER(D1:D10000),(A1:A10000="file:")*D1:D10000))

Array to be confirmed with control+shift+enter

Hope that helps
Click to expand...

----------
Thanks for trying.
I have too many lines like this in the spreadsheet, this is why I am looking for an automatic command (without confirmation of the array) that should be pasted to the whole column, and give an answer only in the lines in which the "file: word exist.
Koby.
 
Upvote 0
You could try this:

Assumes the data table starts in column A

Code: 
Sub test()

Dim lRow As Integer
Dim wSht As Worksheet
Dim x As Integer
Dim y As Double

Set wSht = ActiveSheet

lRow = wSht.Cells(wSht.Rows.Count, 1).End(xlUp).Row

For x = lRow To 2 Step -1
    If Left(Cells(x, 2).Value, 5) = "file:" Then
        Cells(x, 4).Value = y
        y = 0
            Else
        y = y + Cells(x, 4).Value
    End If
Next x

End Sub
 
Upvote 0
kobyb said:
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]
Click to expand...

Care to post the results that you want to see?
 
Upvote 0
Thanks for looking at it.
The result should be posted into the cell. In the example, the cell having now "#VALUE" on the row starting with 62 should contain the number "7", and the one on the raw starting with 70 will contain "5". I prefer to have it without VB.
Koby.
 
Upvote 0
kobyb said:
Thanks for looking at it.
The result should be posted into the cell. In the example, the cell having now "#VALUE" on the row starting with 62 should contain the number "7", and the one on the raw starting with 70 will contain "5". I prefer to have it without VB.
Koby.
Click to expand...
One way( non array)

Excel 2010
ABCDEF
359file:-89077~1.CAC3
46010711
56111824
662file:-25634~1.CAC7
763259611
864259911
965260111
1066260311
1167260511
1268260711
1369260911
1470file:-88614~1.CAC5
15712558525
1672file:838425~1.CAC5
17712558525
1872file:838425~1.CAC
19
20
Sheet10
Cell Formulas
RangeFormula
E3=IF(B3="file:",SUM(D4:INDEX(D4:$D$18,MATCH(TRUE,INDEX(D4:$D$18="",),0))),"")
 
Upvote 0
kobyb said:
Thanks for looking at it.
The result should be posted into the cell. In the example, the cell having now "#VALUE" on the row starting with 62 should contain the number "7", and the one on the raw starting with 70 will contain "5". I prefer to have it without VB.
Koby.
Click to expand...

Data is assumed to be in B:D, starting at row 57, the headers in row 56...
[TABLE="width: 218"]
<colgroup><col style="width: 80pt; mso-width-source: userset; mso-width-alt: 3811;" width="107"> <col style="width: 103pt; mso-width-source: userset; mso-width-alt: 4864;" width="137"> <col style="width: 35pt; mso-width-source: userset; mso-width-alt: 1678;" width="47"> <tbody>[TR]
[TD="class: xl64, width: 107, bgcolor: transparent"]Field-1[/TD]
[TD="class: xl64, width: 137, bgcolor: transparent"]Field-2[/TD]
[TD="class: xl64, width: 47, bgcolor: transparent"]Field-3[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 107, bgcolor: white"]file:[/TD]
[TD="class: xl63, width: 137, bgcolor: white"]HIRES-~1[/TD]
[TD="class: xl65, width: 47, bgcolor: white"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 107, bgcolor: white"]208[/TD]
[TD="class: xl63, width: 137, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 47, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 107, bgcolor: white"]file:[/TD]
[TD="class: xl63, width: 137, bgcolor: white"]-89077~1.CAC[/TD]
[TD="class: xl65, width: 47, bgcolor: white"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 107, bgcolor: white"]107[/TD]
[TD="class: xl63, width: 137, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 47, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 107, bgcolor: white"]118[/TD]
[TD="class: xl63, width: 137, bgcolor: white"]2[/TD]
[TD="class: xl65, width: 47, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 107, bgcolor: white"]file:[/TD]
[TD="class: xl63, width: 137, bgcolor: white"]-25634~1.CAC[/TD]
[TD="class: xl65, width: 47, bgcolor: white"]7[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 107, bgcolor: white"]2596[/TD]
[TD="class: xl63, width: 137, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 47, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 107, bgcolor: white"]2599[/TD]
[TD="class: xl63, width: 137, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 47, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 107, bgcolor: white"]2601[/TD]
[TD="class: xl63, width: 137, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 47, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 107, bgcolor: white"]2603[/TD]
[TD="class: xl63, width: 137, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 47, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 107, bgcolor: white"]2605[/TD]
[TD="class: xl63, width: 137, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 47, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 107, bgcolor: white"]2607[/TD]
[TD="class: xl63, width: 137, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 47, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 107, bgcolor: white"]2609[/TD]
[TD="class: xl63, width: 137, bgcolor: white"]1[/TD]
[TD="class: xl65, width: 47, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 107, bgcolor: white"]file:[/TD]
[TD="class: xl63, width: 137, bgcolor: white"]-88614~1.CAC[/TD]
[TD="class: xl65, width: 47, bgcolor: white"]5[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 107, bgcolor: white"]2558[/TD]
[TD="class: xl63, width: 137, bgcolor: white"]5[/TD]
[TD="class: xl65, width: 47, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 107, bgcolor: white"]file:[/TD]
[TD="class: xl63, width: 137, bgcolor: white"]838425~1.CAC[/TD]
[TD="class: xl65, width: 47, bgcolor: white"]0[/TD]
[/TR]
</tbody>[/TABLE]


D2, just enter & copy down:
Rich (BB code): 
=IF(B57="file:",SUM(C57:INDEX(C58:$C$72,MATCH("file:",B58:$B$72,0))),"")

If necessary, the set up can be modified to process a dynamic instead of a fixed range.
 
Upvote 0
Thanks.
It looks like a very close solution. A very neat one..
I could understand why it stops in the middle(I have almost 5000 rows in the spreadsheet) but could not understand why there is a numeric value in the "C" column on the "file:" row, the calculation adds it to the sum?.
Thanks,
Koby.
 
Upvote 0
kobyb said:
Thanks.
It looks like a very close solution. A very neat one..
I could understand why it stops in the middle(I have almost 5000 rows in the spreadsheet) but could not understand why there is a numeric value in the "C" column on the "file:" row, the calculation adds it to the sum?.
Thanks,
Koby.
Click to expand...

To which solution do you refer?
Can you make this more clearer?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC
T
  • Question Question
cycle through multiple sheets
Replies
6
Views
760
ttowncorp
T

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top