This is a pretty standard use of the SUMIFS function. In this case, we need to sum amounts based on two criteria: type (forecast or actual) and group. To sum by type, the range/criteria pair is:
where type is the named range D5:D14, and G4 is a mixed reference with the row locked in order to match the column header in row 4 when the formula is copied down.
To sum by group, the range/criteria pair is:
where group is the named range B5:B14, and F5 is a mixed reference with the column locked in order to match group names in column F when the formula is copied across.
The variance formula in column I simply subtracts forecast from actual:
The variance percentage formula in column J is:
with percentage number format applied.
- The data shown here would work well in an Excel Table, which would automatically expand to include new data. We are using named ranges here to keep the formulas as simple as possible.
- Pivot tables can also be used to calculate variance. Formulas provide more flexibility and control at the cost of more complexity.