CODEWITHSUNDEEP
arraysexcel
M. Matador
M. Matador

Reputation: 3

Need an array formula in excel for unique sequenced values

I need a formula to group sequenced data and then calculate the sum of each sequence. so I need an array formula to calculate "each sequence" & "sum" column without "sequence" column as shown in the image attached.

enter image description here

Upvotes: 0

Views: 71

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

You could do it with offset ranges:

In F2:

=IFERROR(INDEX(A:A,SMALL(IF(A$2:A$18<>A$1:A$17,ROW(A$2:A$18)),ROW(A1))),"")

in G2:

=SUM(INDEX(B:B,SMALL(IF(A$2:A$18<>A$1:A$17,ROW(A$2:A$18)),ROW(A1))):INDEX(A:A,SMALL(IF(A$2:A$18<>A$3:A$19,ROW(A$2:A$18)),ROW(A1))))

In other words, you're detecting where each block starts and ends.

enter image description here

I've only tested these formulas in Microsoft 365 - you probably need to array-enter them in Excel 2019.

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

No need for an array formula. A simple SUMIF will do it:

G2: =SUMIF(Sequence,ROWS($1:1),Score)

and fill down as needed.

Where

enter image description here

There is no requirement that the Sequence column be visible. It does need to be the same size and dimension as the Score column, but could be located anyplace, and hidden.

Upvotes: 0

Related Questions