I don't understand SUMPRODUCT apparently

[Technetium]

I understand that if I have two rows, let's say A1:G1 that contain prices, and A2:G2 that contain amounts, I can use SUMPRODUCT(A1:G1,A2:G2) to get a total value of all items. This is a very basic textbook example of SUMPRODUCT. I have used it many times in this type of way.

What I don't understand... Suppose I have A1:G1 that contains prices. Then I have A2:G20 that contains rows of orders with amounts. I want to get the total value of all orders. So what I need is a sum of each column, multiplied by each matching price, and then all those results added together. I have to use SUMPRODUCT and I can't create any helper cells. What I tried to do first was:

SUMPRODUCT(A1:G1,{SUM(A2:A20),SUM(B2:B20),SUM(C2:C20),SUM(D2:D20),SUM(E2:E20),SUM(F2:F20),SUM(G2:G20)})

But this generates a formula error. I thought I could define an array that way but I guess not.

I was stuck for a while until I gave up and looked at the answer, which is SUMPRODUCT(A1:G1*A2:G20). I need an explanation why this gives the total value of all orders. I just don't understand why this works. For one thing it only has 1 argument. I don't understand multiplying the two arrays that way (I thought the whole point of SUMPRODUCT was to do the array multiplication with the arrays as separate arguments).

[CARACALLA]

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[Technetium]

Sure. Attached to this post. I changed the cell references to add headers and stuff so it's less confusing. Please check the cell formula in H2. What I would like to understand is why that is the correct formula to find the total value of all the items ordered in all of the orders. When you look up reference information on how SUMPRODUCT works, this is not what you find (usually you find SUMPRODUCT(array1, array2)). I would have never arrived at this conclusion myself for this problem. So I'm trying to understand how you would reason out this solution so that I can actually understand it going forward.

[FlameRetired]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Technetium]

I don't really have any idea what to put for the thread title. I am just looking for someone who can explain the specific formula I mentioned in the previous message. I guess I will ask somewhere else, thanks.