Struggling. Find value in Row G. Then Add all data from Column C down to where found value is.

Occasional Visitor

Cory_McC_0-1662072057360.png

In this spreadsheet.

Lookup a value from another tab.

Say 12

Search for that in Column G

When find 12, Add all the value in Column C down to Row 24.

 

So in real language, trying to add up all the Interest charged for 12 Months, and return that onto another spreadsheet cell.

 

thanks

 

1 Reply

@Cory_McC 

 

In the attached, I've just put a value into cell L1 to represent the value you want to retrieve from elsewhere.

 

Then use this formula in cell J5 to sum the values in column C from cell C3 down to the row corresponding to where the value in L1 is found in column G.

=SUM(C3:INDIRECT("C"&TEXT(MATCH(L1,G3:G46,0)+2,"0")))

 

This works by:

  • the MATCH function locates the row in the column where the value in L1 is found
  • I then add 2 to that value (because the first row in the column is row 3: you'll have to adjust that to correspond to whatever row your month 1 appears in)
  • the TEXT function turns that number into a text string
  • INDIRECT then constructs the second reference for the SUM function
  • so that, in effect, for the 12th month, it reads SUM(C3:C14)