0

Here is my data:

I'm looking for an Excel formula that would calculate the sum of all values in a column, values situated right under every match of a string in that column.
So formula in B1 should return 17 (1+6+10).
It would be great to also pass to the formula the desired row offset of summed values, if possible (this would be 1 in my example).

GSerg
  • 76,472
  • 17
  • 159
  • 346
user2285985
  • 79
  • 1
  • 6

2 Answers2

1

Faster, but without explicit offset:

=SUMIF(A1:A17,"A",A2:A18)

Slower, but with explicit offset:

=SUMIF(A1:A17,"A",OFFSET(A1:A17,1,0))
GSerg
  • 76,472
  • 17
  • 159
  • 346
0

=SUM(INDEX($B$1:$B$17,IF($A$1:$A$17="A",ROW($B$1:$B$17)+1)))

Or if you list the letters in column D: =SUM(INDEX($B$1:$B$17,IF($A$1:$A$17=D1,ROW($B$1:$B$17)+1))) enter image description here

P.b
  • 8,293
  • 2
  • 10
  • 25