Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
Whoops, I meant greater than 0. Formula works fine! Thanks for your
help Pete. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|