Using Specifiers in Tables for Formulas, Excel 2019

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 

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]

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.

When you create an Excel table, Excel assigns a name to the table, and to each column header in the table. When you add formulas to an Excel table, those names can appear automatically as you enter the formula and select the cell references in the table instead of manually entering them. Here's an example of what Excel does:

Instead of using explicit cell references

Excel uses table and column names

=Sum(C2:C7)

=SUM(DeptSales[Sales Amount])]

That combination of table and column names is called a structured reference. The names in structured references adjust whenever you add or remove data from the table.

Structured references also appear when you create a formula outside of an Excel table that references table data. The references can make it easier to locate tables in a large workbook.

Regards,
Jerry