0

This is what the current sheet looks like, it is a Raw response log from a google form.

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.

2 Answers 2

0

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.

You can read more about SUMPRODUCT

EXAMPLE

sample

3
  • 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. Dec 13, 2021 at 21:51
  • We've all been there and done that. If this answer was helpful, please consider "accepting" it.
    – Tedinoz
    Dec 13, 2021 at 21:55
  • All done, thanks! Dec 13, 2021 at 22:29
0

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)))})

1
  • 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. Dec 14, 2021 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.