How to summarize the first 4 numbers greater than 0 from a list.

I've a list of numbers, I need to summarize the first 4 greater than zero
cronologically eventhough they were in different places, and it could happen
only three or less were in the whole list.
Ex.
2 0 0 0 8 -3 -5 0 4 0 0 0 3 0 0 0 0 7 8 9 4 5 1 2 -8 -7

Sum. 2 + 8 + 4 + 3 = 17

easy hu? Thanks

One way, assume your data starts in A1 going down to A26

=SUM(TRANSPOSE(OFFSET(A1,SMALL(IF((A1:A26>0),ROW(A1:A26)),{1,2,3,4})-1,,ROWS(A1:A26),)))

entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

(No private emails please)


"Antonio" <[email protected]> wrote in message
news:[email protected]...
> I've a list of numbers, I need to summarize the first 4 greater than zero
> cronologically eventhough they were in different places, and it could
> happen
> only three or less were in the whole list.
> Ex.
> 2 0 0 0 8 -3 -5 0 4 0 0 0 3 0 0 0 0 7 8 9 4 5 1 2 -8 -7
>
> Sum. 2 + 8 + 4 + 3 = 17
>
> easy hu? Thanks

Try...

=SUM(SUBTOTAL(9,OFFSET(A1:A100,SMALL(IF(A1:A100>0,ROW(A1:A100)-ROW(A1)),{
1,2,3,4}),0,1)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range
accordingly.

Hope this helps!

In article <[email protected]>,
"Antonio" <[email protected]> wrote:

> I've a list of numbers, I need to summarize the first 4 greater than zero
> cronologically eventhough they were in different places, and it could happen
> only three or less were in the whole list.
> Ex.
> 2 0 0 0 8 -3 -5 0 4 0 0 0 3 0 0 0 0 7 8 9 4 5 1 2 -8 -7
>
> Sum. 2 + 8 + 4 + 3 = 17
>
> easy hu? Thanks

Make that...

=SUM(SUBTOTAL(9,OFFSET(A1:A100,SMALL(IF(A1:A100>0,ROW(A1:A100)-ROW(A1)),R
OW(INDIRECT("1:"&MIN(4,COUNTIF(A1:A10,">0"))))),0,1)))

....confirmed with CONTROL+SHIFT+ENTER.

In article <[email protected]>,
Domenic <[email protected]> wrote:

> Try...
>
> =SUM(SUBTOTAL(9,OFFSET(A1:A100,SMALL(IF(A1:A100>0,ROW(A1:A100)-ROW(A1)),{
> 1,2,3,4}),0,1)))
>
> ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range
> accordingly.
>
> Hope this helps!
>
> In article <[email protected]>,
> "Antonio" <[email protected]> wrote:
>
> > I've a list of numbers, I need to summarize the first 4 greater than zero
> > cronologically eventhough they were in different places, and it could
> > happen
> > only three or less were in the whole list.
> > Ex.
> > 2 0 0 0 8 -3 -5 0 4 0 0 0 3 0 0 0 0 7 8 9 4 5 1 2 -8 -7
> >
> > Sum. 2 + 8 + 4 + 3 = 17
> >
> > easy hu? Thanks

Thanks Peo but it's not working right, let me sent you attached the real
table I have and the "total value" I need to have:
Cat# A B C D E F G H Total
AF40 -20 20 0 10 -10 0 10 10 50
AF50 10 0 0 -60 0 -40 0 10 20
AF60 0 0 0 0 0 20 20 10 50
RT50 10 20 40 10 50 90 50 10 80
RT60 0 0 0 -50 0 -30 0 0 0
V100 20 20 0 0 0 -10 40 60 140

I'm summarizing only the first 4 possitives numbers in the row. ex. row 1:
B2(20)+D2(10)+G2(10)+H2(10)=50


thanks again for your help.
Antonio



"Peo Sjoblom" wrote:

> One way, assume your data starts in A1 going down to A26
>
> =SUM(TRANSPOSE(OFFSET(A1,SMALL(IF((A1:A26>0),ROW(A1:A26)),{1,2,3,4})-1,,ROWS(A1:A26),)))
>
> entered with ctrl + shift & enter
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "Antonio" <[email protected]> wrote in message
> news:[email protected]...
> > I've a list of numbers, I need to summarize the first 4 greater than zero
> > cronologically eventhough they were in different places, and it could
> > happen
> > only three or less were in the whole list.
> > Ex.
> > 2 0 0 0 8 -3 -5 0 4 0 0 0 3 0 0 0 0 7 8 9 4 5 1 2 -8 -7
> >
> > Sum. 2 + 8 + 4 + 3 = 17
> >
> > easy hu? Thanks
>
>

Thanks Domenic but the values are getting are incorrect, let me attach part
of the table I'm working on.
Cat# A B C D E F G H Total
AF40 -20 20 0 10 -10 0 10 10 50
AF50 10 0 0 -60 0 -40 0 10 20
AF60 0 0 0 0 0 20 20 10 50
RT50 10 20 40 10 50 90 50 10 80
RT60 0 0 0 -50 0 -30 0 0 0
V100 20 20 0 0 0 -10 40 60 140

I hope this can help you better, thanks again!!
Antonio



"Domenic" wrote:

> Try...
>
> =SUM(SUBTOTAL(9,OFFSET(A1:A100,SMALL(IF(A1:A100>0,ROW(A1:A100)-ROW(A1)),{
> 1,2,3,4}),0,1)))
>
> ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range
> accordingly.
>
> Hope this helps!
>
> In article <[email protected]>,
> "Antonio" <[email protected]> wrote:
>
> > I've a list of numbers, I need to summarize the first 4 greater than zero
> > cronologically eventhough they were in different places, and it could happen
> > only three or less were in the whole list.
> > Ex.
> > 2 0 0 0 8 -3 -5 0 4 0 0 0 3 0 0 0 0 7 8 9 4 5 1 2 -8 -7
> >
> > Sum. 2 + 8 + 4 + 3 = 17
> >
> > easy hu? Thanks
>

First, define the following reference...

Select J2

Insert > Name > Define

Name: Num

Refers to:

=ROW(INDIRECT("1:"&MIN(4,COUNTIF(Sheet1!$B2:$I2,">0"))))

Click Ok

Then, enter the following formula in J2, and copy down:

=IF(COUNTIF(B2:I2,">0"),SUM(SUBTOTAL(9,OFFSET(B2:I2,,SMALL(IF(B2:I2>0,COL
UMN(B2:I2)-COLUMN(B2)),Num),,1))),0)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <[email protected]>,
"Antonio" <[email protected]> wrote:

> Thanks Domenic but the values are getting are incorrect, let me attach part
> of the table I'm working on.
> Cat# A B C D E F G H Total
> AF40 -20 20 0 10 -10 0 10 10 50
> AF50 10 0 0 -60 0 -40 0 10 20
> AF60 0 0 0 0 0 20 20 10 50
> RT50 10 20 40 10 50 90 50 10 80
> RT60 0 0 0 -50 0 -30 0 0 0
> V100 20 20 0 0 0 -10 40 60 140
>
> I hope this can help you better, thanks again!!
> Antonio
>
>
>
> "Domenic" wrote:
>
> > Try...
> >
> > =SUM(SUBTOTAL(9,OFFSET(A1:A100,SMALL(IF(A1:A100>0,ROW(A1:A100)-ROW(A1)),{
> > 1,2,3,4}),0,1)))
> >
> > ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range
> > accordingly.
> >
> > Hope this helps!
> >
> > In article <[email protected]>,
> > "Antonio" <[email protected]> wrote:
> >
> > > I've a list of numbers, I need to summarize the first 4 greater than zero
> > > cronologically eventhough they were in different places, and it could
> > > happen
> > > only three or less were in the whole list.
> > > Ex.
> > > 2 0 0 0 8 -3 -5 0 4 0 0 0 3 0 0 0 0 7 8 9 4 5 1 2 -8 -7
> > >
> > > Sum. 2 + 8 + 4 + 3 = 17
> > >
> > > easy hu? Thanks
> >

Try

=SUM(TRANSPOSE(OFFSET(A2,,SMALL(IF((B2:IV2>0),COLUMN(B2:IV2)),{1,2,3,4})-1,,COUNT(B2:IV2))))

entered with ctrl + shift & enter

if there are less than 4 positive values it will return an error,


=SUM(TRANSPOSE(OFFSET(A2,,SMALL(IF((B2:IV2>0),COLUMN(B2:IV2)),ROW(INDIRECT("1:"&MIN(4,COUNTIF(B2:IV2,">0")))))-1,,COUNT(B2:IV2))))

will avoid an error if less than 4,

--
Regards,

Peo Sjoblom

(No private emails please)


"Antonio" <[email protected]> wrote in message
news:[email protected]...
> Thanks Peo but it's not working right, let me sent you attached the real
> table I have and the "total value" I need to have:
> Cat# A B C D E F G H Total
> AF40 -20 20 0 10 -10 0 10 10 50
> AF50 10 0 0 -60 0 -40 0 10 20
> AF60 0 0 0 0 0 20 20 10 50
> RT50 10 20 40 10 50 90 50 10 80
> RT60 0 0 0 -50 0 -30 0 0 0
> V100 20 20 0 0 0 -10 40 60 140
>
> I'm summarizing only the first 4 possitives numbers in the row. ex. row
> 1:
> B2(20)+D2(10)+G2(10)+H2(10)=50
>
>
> thanks again for your help.
> Antonio
>
>
>
> "Peo Sjoblom" wrote:
>
>> One way, assume your data starts in A1 going down to A26
>>
>> =SUM(TRANSPOSE(OFFSET(A1,SMALL(IF((A1:A26>0),ROW(A1:A26)),{1,2,3,4})-1,,ROWS(A1:A26),)))
>>
>> entered with ctrl + shift & enter
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>> (No private emails please)
>>
>>
>> "Antonio" <[email protected]> wrote in message
>> news:[email protected]...
>> > I've a list of numbers, I need to summarize the first 4 greater than
>> > zero
>> > cronologically eventhough they were in different places, and it could
>> > happen
>> > only three or less were in the whole list.
>> > Ex.
>> > 2 0 0 0 8 -3 -5 0 4 0 0 0 3 0 0 0 0 7 8 9 4 5 1 2 -8 -7
>> >
>> > Sum. 2 + 8 + 4 + 3 = 17
>> >
>> > easy hu? Thanks
>>
>>