Count or sum variance

In this example, the goal is to sum or count a set of variances in different ways. Variances are listed in D5:D15, which is also the named range variance. The first formula in F5 simply sums all variances with the SUM function.

=SUM(variance) // returns -175

Sum absolute variances

The formula in F6 calculates the sum of absolute variances with the ABS function together with the SUMPRODUCT function:

=SUMPRODUCT(ABS(variance)) // returns 975

In this formula, ABS returns the absolute value of variances to SUMPRODUCT in a single array:

=SUMPRODUCT({25;150;200;225;50;100;25;75;0;75;50})

SUMPRODUCT then returns the sum, 975.

Note: we use the SUMPRODUCT function here instead of the SUM function because SUMPRODUCT can handle many array operations natively without entering the formula in a special way. This means it will work in any version of Excel without special handling. See Why SUMPRODUCT for more information.

Count non-zero variance

The formula in F7 counts the number of absolute variances that are greater than zero (0):

=SUMPRODUCT(--(ABS(variance)>0)) // returns 10

In this formula, ABS returns the absolute values for all variances in an array as explained above:

{25;150;200;225;50;100;25;75;0;75;50}

A logical expression is used to check for variances greater than zero:

{25;150;200;225;50;100;25;75;0;75;50}>0

This returns an array of TRUE and FALSE values:

--{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE}

The double negative (--) converts the TRUE and FALSE values to 1s and 0s and the result is delivered directly to the SUMPRODUCT function:

=SUMPRODUCT({1;1;1;1;1;1;1;1;0;1;1}) // returns 10

which returns a final result of 10.

Count positive and negative variances

The formula in F8 counts the number of positive variances:

=SUMPRODUCT(--(variance>0))
=SUMPRODUCT(--({25;-150;200;-225;-50;100;-25;75;0;-75;-50}>0))
=SUMPRODUCT({1;0;1;0;0;1;0;1;0;0;0})
=4

The formula in F9 counts negative variances:

=SUMPRODUCT(--(variance<0))
=SUMPRODUCT(--({25;-150;200;-225;-50;100;-25;75;0;-75;-50}<0))
=SUMPRODUCT({0;1;0;1;1;0;1;0;0;1;1})
=6

Count absolute variance greater than 100

Finally, the formula in F10 counts absolute variances greater than 100:

=SUMPRODUCT(--(ABS(variance)>100))
=SUMPRODUCT(--({25;150;200;225;50;100;25;75;0;75;50}>100))
=SUMPRODUCT({0;1;1;1;0;0;0;0;0;0;0})
=3

Direct array operation

In the example shown, the variances in column D act as a helper column. However, you can calculate the variances directly in an array operation if needed with the same results. For example, to count positive variances, the formula in F8 is:

=SUMPRODUCT(--(variance>0)) // returns 4

This formula can be rewritten to calculate variance internally like this:

=SUMPRODUCT(--(C5:C15-B5:B15>0)) // returns 4

The named range variance can be replaced with C5:C15-B5:B15 in all formulas above.