It seems fairly clear what you have and need.
What you are calling a "row reference" would usually be called a "lookup value."
There are a variety of ways to lookup values in a column (or row, if needed). Today (2022) we'd likely use XLOOKUP() but that was not available in 2016. Then, the two main approaches were VLOOKUP() and INDEX/MATCH.
VLOOKUP() would be great at finding the values you call row references, usually, but apparently you might see insertion and deletion of columns. It does not do as well in that kind of case though IT CAN do so with appropriate planning. But why do that planning if you don't need to? (I'm a big fan of it, so that sentence was hard to commit to print, so to speak.)
So, INDEX/MATCH. Given your use of "row reference" I assume you are not overly familiar with Excel. So I'll give a basic explanation.
Let's say your "row references" are in column C, starting in row 1 and going to, say, row 1000. Also, that you currently have 23 columns of products, currently. So you use columns C:Z (1 + 23 = 24) and the 1000 rows. That range would be written as C1:Z1000 in Excel.
Next, I'll say you place the value you want looked up in cell A1 and your formulas for finding the sums you desire are in cells B1 and B2 (so they physically match the way they appear in the data range (C1:Z1000).
So, you place a value in A1 and the desired results appear in B1 and B2.
The formula for that would be:
=INDEX(D1:Z1000, MATCH(A1, C1:C1000, 0), 0)
What it does is the inside function, MATCH(), looks for the value you entered in A1 in column C where those values appear. It will return a number back to the formula that is the "however many-eth row" it finds the value on. So if the lookup value ("row reference") is in row 397, it will return "397" to the formula. That is probably clear now, looking at the first two parameters in it, "A1" and "C1:C1000" but there is a third parameter also: "0" and that tells MATCH() to find an exact match, not one that is just close.
So after it finds it (in row 397 in this example), the formula effectively becomes:
=INDEX(D1:Z1000, 397, 0)
INDEX() is a function that is given a range of data, D1:Z1000 in this case, and then a row number and a column number. In this example, the row number is 397 and the column number is... special. Using a "0" which seems to make no sense actually tells it to return ALL the columns in the row.
At this moment, let me draw attention to the fact INDEX() is given the range D1 to Z1000, NOT C1 to Z1000. That is because you don't want to get the column with the "row references" in the formula's output since perhaps it could have numbers as well as text and then those numbers could become part of the sum, making it wrong.
So, INDEX() will take the row number found by MATCH() and return all the product values in that row, but not the "row reference" value in it.
So far, so good. And there is one more thing to do with the formula after it returns the row's information. But before adding that element, let me address the key problem you are having, that of getting the second row's values to sum them up.
You probably see what to do already, but in case not, you do exactly the above, except you add "1" to whatever MATCH() returns, like so:
=INDEX(D1:Z1000, MATCH(A1, C1:C1000, 0) + 1, 0)
and now it will return "398" to INDEX() instead of "397" and will so collect the information in the row immediately following the row holding the "row reference" that was looked up.
Cell B1 gets the version that does not add 1 and cell B2 gets the version that does add the 1.
Last step is to SUM() the result of the INDEX/MATCH part:
=SUM( INDEX(D1:Z1000, MATCH(A1, C1:C1000, 0), 0) )
and
=SUM( INDEX(D1:Z1000, MATCH(A1, C1:C1000, 0)+1, 0) )
So all pretty straightforward: use MATCH() to find the row the "row reference" is in, the use that for the balances, or add 1 to it for the rates, then use INDEX() to collect all the row's data, and finally use SUM() to sum them up.
One other thing to address. That is how to handle the insertion or deletion of columns that could take place over time.
There are several tried-and-true ways to do so. One is to simply make the range INDEX() uses wider than needed, enough wider that you don't need to check that it's still OK very often, so in our example, perhaps instead of ending it with column Z, you might end it with column BZ, adding 52 columns, and so you're good until you delete at least 52 columns. This approach is really aimed at the situation in which you don't actually insert columns for new products, but rather you simply go past the last product and add a new product name at the top of the next column.
If you can be sure you will always insert columns when adding products, then you can use a variation on the above idea, just adding one column to the range, so C1:AA1000, and never, ever, just go to the next column and add a product name. You would always insert a column. Then Excel will change the range's size for the extra column/s you added, no work on your part required.
Excel will take care of column deletions in the same way regardless of how you handle the insertions.
There are other ways to do it but when starting out, a straightforward approach is easy to understand and work with.
