Forum Discussion

Cory_McC's avatar
Cory_McC
Copper Contributor
Sep 01, 2022

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

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

 

excel
Formulas and Functions

1 Reply

Sort By
  • mathetes's avatar
    mathetes
    Silver Contributor
    Sep 02, 2022

    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)

Resources