Question

On the Farmers Market worksheet, what should be inserted to calculate the Sum in the Sales column for each Location using Subtotal?

a) =SUBTOTAL(9, Sales)
b) =SUBTOTAL(109, Sales)
c) =SUM(Sales)
d) =TOTAL(Sales)

Answer 1

The correct function to calculate the Sum in the Sales column for each Location using Subtotal on the Farmers Market worksheet is =SUBTOTAL(9, Sales). This function will sum the amounts in the Sales column while ignoring any hidden rows. The number 9 in the SUBTOTAL function is used specifically for summing up visible cells.

Step-by-step explanation:

To calculate the Sum in the Sales column for each Location using Subtotal on the Farmers Market worksheet, the correct function to insert is =SUBTOTAL(9, Sales). Option (a) is correct because the number 9 specifies that the SUBTOTAL function should use the SUM function for the aggregation. This will calculate the sum of the Sales column while ignoring any rows that have been hidden, providing a subtotal for visible cells only.

On the other hand, option (b) =SUBTOTAL(109, Sales) also uses the SUBTOTAL function, but the number 109 specifies that it should include only visible cells when calculating the sum and ignore filtered-out cells, not just hidden ones. If you are not filtering your data, then options (a) and (b) would effectively give you the same result. However, the typical use of SUBTOTAL with 9 is for cases where you do not anticipate filtering.

Option (c), =SUM(Sales), would sum all cells in the Sales column regardless of whether they are hidden or not, and option (d), =TOTAL(Sales), is not a valid function in Excel.