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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Antonio - 24 Nov 2005 19:22 GMT

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

Reply to this Message

Peo Sjoblom - 24 Nov 2005 19:47 GMT

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

Signature

Regards,

Peo Sjoblom

(No private emails please)

> 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
[quoted text clipped - 6 lines]
>
> easy hu? Thanks

Reply to this Message

Antonio - 24 Nov 2005 22:56 GMT

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

> One way, assume your data starts in A1 going down to A26
>
[quoted text clipped - 12 lines]
> >
> > easy hu? Thanks

Reply to this Message

Peo Sjoblom - 24 Nov 2005 23:34 GMT

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,

Signature

Regards,

Peo Sjoblom

(No private emails please)

> 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:
[quoted text clipped - 30 lines]
>> >
>> > easy hu? Thanks

Reply to this Message

Domenic - 24 Nov 2005 19:49 GMT

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!

> 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
[quoted text clipped - 5 lines]
>
> easy hu? Thanks

Reply to this Message

Domenic - 24 Nov 2005 19:58 GMT

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.

> Try...
>
[quoted text clipped - 16 lines]
> >
> > easy hu? Thanks

Reply to this Message

Antonio - 24 Nov 2005 23:06 GMT

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

> Try...
>
[quoted text clipped - 15 lines]
> >
> > easy hu? Thanks

Reply to this Message

Domenic - 24 Nov 2005 23:21 GMT

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!

> Thanks Domenic but the values are getting are incorrect, let me attach part
> of the table I'm working on.
[quoted text clipped - 29 lines]
> > >
> > > easy hu? Thanks

Reply to this Message