Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 11:00 AM (PST)
Microsoft Tech Community

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

Copper Contributor


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.




1 Reply



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.



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)