CODEWITHSUNDEEP
excelexcel-formula
Cokeeffe
Cokeeffe

Reputation: 11

Can I sum values within a column between certain starting and end values

Can an dynamic excel formula ( no vba) produce the column c values of 8 and 3? These are generated when the a column value is 1, which triggers the sum of column a values between the 1 value and the next 1 value?

enter image description here

various excel functions, sum if, sum product. Could not make it work.

Upvotes: 0

Views: 256

Answers (5)

user22566114
user22566114

Reputation: 391

Alternatively you can enter this formula in cell B2:

=BYROW(A2:A8,LAMBDA(x,IF(AND(x=1,OFFSET(x,1,0)<>1),IFERROR(SUM(INDEX($A$2:$A$8,ROW(x)):INDEX($A$2:$A$8,IF(A2=1,SMALL(IF(x:$A$8=1,ROW(x:$A$8)),2)-2,""))),""),"")))

I can't add a screenshot since i'm a new user. The data is exactly as in the screenshot of the questioner. In cells A1 to A8 are the values 0, 1, 3, 5, 1, 3, 1 and 1.

Another alternative could be this formula in cell B2:

=BYROW(A2:A8,LAMBDA(x,IF(AND(x=1,OFFSET(x,1,0)<>1),SUM(OFFSET(x,1,0):XLOOKUP(1,OFFSET(x,1,0):A8,OFFSET(x,1,0):A8))-1,"")))

Upvotes: 0

JvdV
JvdV

Reputation: 75960

You can try the following:

enter image description here

Formula in B1:

=MAP(A1:A7,A2:A8,LAMBDA(x,y,IF(x=1,IF(y<>1,SUM(y:XLOOKUP(1,y:A8,x:A7,y)),""),"")))

Upvotes: 1

BeXXsoR
BeXXsoR

Reputation: 114

Put this into B2 and paste it to all the cells until B8:

=IF(A2=1,SUM(IF(ROW(A3:A$8)>=MIN(IF(A3:A$8=1,ROW(A3:A$8),"")),0,A3:A$8)),"")

Explanation: Let's start with the inner part and expand it to the outer ones:

  1. MIN(IF(A3:A$8=1,ROW(A3:A$8),"")) This returns the minimal row number of all the rows that contain a 1, starting in row 3 (i.e. the row after the current cell). This corresponds to the row number of the row that contains the next 1. (e.g. 5 for cell B2)
  2. IF(ROW(A3:A$8)>=...,0,A3:A$8): This compares the row number of all cells after the current one with the row number that was returned by step 1. If the row number of a cell is higher, it returns 0, otherwise it returns the vlaue of the cell. So this returns an internal array in which the values of all cells that are equal to or behind the next 1 are set to 0. (e.g. [3;5;0;0;0;0] for cell B2)
  3. SUM(...): This just adds up all values of the result from step 2. This is exactly the outcome that we want (i.e. 8 for cell B2)
  4. IF(A2=1,...,""): This makes sure that steps 1-3 are only executed if the value in column A is a 1, otherwise the cell in column B will be left blank.

Upvotes: 1

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27438

Paste this in cell B2

=LET (arr, SCAN (0, A2:A9, LAMBDA (initial_value, array, (array=1)*1+initial_value)), sum, MMULT (N (arr=TRANSPOSE (arr)), A2:A9)-1, (A2:A9=1)*sum)

Here is an explanation

  • The scan function assigns new value to all the 1s in the range i. e. It looks for 1 in the range and return 1 plus initial value else just initial value
  • The MMULT is used to calculate matrix product N (arr=TRANSPOSE (arr)) and A2:A9, the result gets subtracted by 1 (because we are looking for 1) to retain the actual sum.
  • lastly if the A2:A9 equal to 1 which creates a Boolean array where all the true equals to one is multiplied to the MMULT result otherwise it returns 0 as expected.

May be not the best of explanation, but the formula works as expected.

Upvotes: 0

Grant
Grant

Reputation: 115

Paste this into B2 and fill down:

=IFERROR(IF(A2=1,SUM(INDEX(FILTER(A3:$A$9,A3:$A$9<>""),SEQUENCE(XMATCH(1,A3:$A$9)-1,1,1,1))),""),"")

What it's doing: IFERROR catches the last 1's in the column where there's no non-1s afterward. The INDEX+FILTER+MATCH combo comes from here: exceljet.net

The XMATACH returns the number of rows until the next 1.

Upvotes: 1

Related Questions