# Google Sheets SUM of multiple cells which have a monetary value assigned VIA a previous row

I am wondering what the proper equation format would be in order to get Row 2 with its numbers to correctly add up to the proper monetary value based on what is in Row 1. There is 1 of each denomination in the sheet and I want to know how to sum each 1 properly.

Any help would be appreciated, I am going to continue testing.

This is a fairly basic feature of sheets. I'm answering here so that you can learn the principles quickly.

There are more than one way to solve this question.

Option#1: add the product of the cells

This is the formula that most people would use, particularly as they learn how to use Sheets: `=\$B\$1*B2+\$C\$1*C2+\$D\$1*D2+\$E\$1*E2+\$F\$1*F2+\$G\$1*G2+\$H\$1*H2+\$I\$1*I2`

Notice here that:

• the values for Row#1 are expressed as Absolute values
• the values for Row#2 are expressed as Relative values

If you were to add data to Row#3 for another person, and then copy this formula down to row#3, then the result will be accurate.

Option#2: SumProduct

`=sumproduct(\$B\$1:\$I\$1,B2:I2)`

Note again that references for Row#1 are expressed as absolute values, and the references for Row#2 are expressed as Relative values.

EXAMPLE

• Thank you very much for this, you are a life saver! I know it is basic, I am trying to get better myself; I just needed this solved relatively quickly. Commented Dec 13, 2021 at 21:51
• We've all been there and done that. If this answer was helpful, please consider "accepting" it. Commented Dec 13, 2021 at 21:55
• All done, thanks! Commented Dec 13, 2021 at 22:29

First, I notice what appears to be a problem with your setup. That is, you don't have a column for \$1 bills or for pennies (\$0.01).

But using only the data you've given and assuming that you'd be wanting to do this for more one register (i.e., obtain a total for each row), you can accomplish this with one single formula instead of dragging a formula down to each person's row.

Delete everything from Col M (including the header) and place the following into cell M1:

`=ArrayFormula({"Total"; IF(C2:C="",, MMULT(E2:L*E1:L1, SEQUENCE(COLUMNS(E:L),1,1,0)))})`

This will create the header (which you can then change within the formula itself if you like) and all results for all rows underneath.

Additionally, if you'd like to see the cumulative total for all rows in the header, you can use this version of the formula:

`=ArrayFormula({"Total: \$"&SUM(E2:L*E1:L1); IF(C2:C="",, MMULT(E2:L*E1:L1, SEQUENCE(COLUMNS(E:L),1,1,0)))})`

• Hey, I live in canada where we do not have 1 and 2 dollar bills or Pennies anymore, I will give your formula a try. Commented Dec 14, 2021 at 18:20