Selected Answer

Rob

Looks like you're doing the built-in Help example for table calculations.

If you had numbers in regular cells (not in a table), normally you'd calculate each commission (= Sales Amount x commission %) then just sum all those numbers.

If you don't want to show all those numbers for some reason, then sum of all commissions could be written most simply as:

`=C2*D2 + C3*D3 + C4*D4 + C5*D5 + C6*D6 + C7*D7 `

etc. That can become very unweildy if you have lots of cells but...

Excel has worksheet function SUMPRODUCT however which multiplies figures from one set of cells (array) with another array (or several arrays) so you get a much shorter equation. The mathematical *product* per row (say) is calculated (like C2*D2) then all such values are added (the sum). The equation becomes just:

`=SUMPRODUCT(D2:D7,E2:E7)`

Inside a table, Excel uses a structured formula like the example in your question. I believe that the second term *[% Commission]* is enclosed in square brackets (unlike the first) since the % sign is somewhere in the name- if you change the heading to remove the % (so it's just Commission) and go to F2 to create the structured multiplication, it will now show:

`=[@Sales Amount]*[@Commission]`

without the second set of square brackets.

Note that you can use the SUMPRODUCT form outside the table. In your sheet, just paste the equation above into a cell in E9 say and the number will appear.

For an example of multiplying (and summing) three numbers, see the attached file where I've added another column (Sales Quantity) so each individual commission is qty x price x commission (%). In the yellow cell is a SUMPRODUCT calculation (away from the table).

Hope this helps.