Find the first value in a row and sum n columns
 

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

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

wrote in message
ups.com...
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

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" wrote in message
...
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

wrote in message
ups.com...
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

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

Pete.

You're welcome. Thanks for the feedback!

Biff

wrote in message
oups.com...
Whoops, I meant greater than 0. Formula works fine! Thanks for your
help

Pete.

Hi,

Try,

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

Array entered.

HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=476336