Forecast vs actual variance

To calculate forecast versus actual variance based on a set of data, you can use can use the SUMIFS function to gather up totals, and basic other formulas to calculate variance and variance percentage. In the example shown, the formula in G5 is:

=SUMIFS(amount,type,G$4,group,$F5)

where amount is the named range C5:C14, and type is the named range D5:D14, and group is the named range B5:B14.

Explanation

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:

type,G$4

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:

group,$F5

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.

Variance formulas

To calculate a variance amount in column I, the formula simply subtracts the forecast from the actual:

=G5-H5

The variance percentage formula in column J is:

=(G5-H5)/H5

with percentage number format applied.

Notes

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.