Find the first value in a row and sum n columns

[[email protected]]

Hello,
Don't know if this easy or not.
I am trying to write a formula that finds the 1st value in a column
that is >0 and then sum that value and the next 4 columns across.
For example my formula is in D2 and I want to find the 1st value to the
right of the formula that is >1 and add up that value and the next 4
values to the right of this

Any help would be appreciated!

Thanks

Pete

[Biff]

Hi!

One way:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=SUM(OFFSET(D2,,MATCH(1,(ISNUMBER(E2:IV2))*(E2:IV2>0),0),,4))

Adjust for the end of the range as needed.

Biff

<[email protected]> wrote in message
news:[email protected]...
> Hello,
> Don't know if this easy or not.
> I am trying to write a formula that finds the 1st value in a column
> that is >0 and then sum that value and the next 4 columns across.
> For example my formula is in D2 and I want to find the 1st value to the
> right of the formula that is >1 and add up that value and the next 4
> values to the right of this
>
> Any help would be appreciated!
>
> Thanks
>
> Pete
>

[Biff]

Hmmm.....

>that finds the 1st value in a column that is >0

>find the 1st value to the right of the formula that is >1

Well, which is it? <g>

I wrote the formula to look for the first number greater than zero.

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> One way:
>
> Entered as an array using the key combo of CTRL,SHIFT,ENTER:
>
> =SUM(OFFSET(D2,,MATCH(1,(ISNUMBER(E2:IV2))*(E2:IV2>0),0),,4))
>
> Adjust for the end of the range as needed.
>
> Biff
>
> <[email protected]> wrote in message
> news:[email protected]...
>> Hello,
>> Don't know if this easy or not.
>> I am trying to write a formula that finds the 1st value in a column
>> that is >0 and then sum that value and the next 4 columns across.
>> For example my formula is in D2 and I want to find the 1st value to the
>> right of the formula that is >1 and add up that value and the next 4
>> values to the right of this
>>
>> Any help would be appreciated!
>>
>> Thanks
>>
>> Pete
>>
>
>

[[email protected]]

Whoops, I meant greater than 0. Formula works fine! Thanks for your
help

Pete.

[Biff]

You're welcome. Thanks for the feedback!

Biff

<[email protected]> wrote in message
news:[email protected]...
> Whoops, I meant greater than 0. Formula works fine! Thanks for your
> help
>
> Pete.
>

[Krishnakumar]

Hi,

Try,

=SUM(INDEX($E$3:$I$3,0,MATCH(TRUE,$E$3:$I$3>0,0)):INDEX($E$3:$I$3,0,MATCH(TRUE,$E$3:$I$3>0,0)+3))

Array entered.

HTH