Summary tables practice exercises
Build the summary a pivot table gives you, with formulas: totals, counts, averages and shares per group, in a table you can fill down and across.
- 1Total per region, ready to fill downIn F2, total the Sales for the region in E2 with SUMIF. Lock the data ranges with $ so the formula can be filled down to F4: =SUMIF($A$2:$A$9,E2,$C$2:$C$9).
- 2A cross-table with mixed referencesPremiumBuild a table with regions down and products across. In F3, use SUMIFS for the region in E3 and the product in F2, with $E3 and F$2 so the formula can be filled down and across.
- 3Orders per region with COUNTIFPremiumIn F2, count the orders for the region in E2 with COUNTIF. Lock the region range with $ so the formula can be filled down.
- 4Average order per regionPremiumIn F2, find the average Sales for the region in E2 with AVERAGEIF, with the data ranges locked so the formula can be filled down.
- 5Each region's share of the totalPremiumIn F2, work out the share of all sales for the region in E2, as a decimal like 0.25: the region's SUMIF total divided by SUM($C$2:$C$9).
- 6Count a cross-table with COUNTIFSPremiumCount the orders with regions down and products across. In F3, use COUNTIFS for the region in E3 and the product in F2, with $E3 and F$2 so the formula can be filled down and across.