2

In Excel sheet 1, I have the following data:

A       B   C   D   E   F   G
------------------------------
Name1   1   2   3   4   5   6
Name2   2   9   3   8   4   7
Name3   4   6   0   3   2   1

In Excel sheet 2, I have to calculate cumulative sum based on values in sheet 1

For example, 

A       B   C   D   E   F   G
------------------------------
Name1   1   3   6   10  15  21

While I can calculate cumulative sum easily, I do not know how to select the correct range of cells from sheet 1, by searching for 'Name1'

Karthik Sankar
  • 21
  • 1

1 Answers1

0

You need a SUMPRODUCT with both relative and absolute column/row cell references.

=SUMPRODUCT(($A2:INDEX($A:$A,MATCH(1E+99,$B:$B))=$I5)*($B2:INDEX(B:B,MATCH(1E+99, B:B))))

enter image description here