Exceljet

Quick, clean, and to the point

Count or sum variance

Excel formula: Count or sum variance
Summary 

To count or sum variances, you can use formulas based on the SUMPRODUCT function and ABS function. In the example shown, the formula in F6 sums absolute variances:

=SUMPRODUCT(ABS(variance))

where variance is the named range D5:D15. In other words, the result is the sum of the values in D5:D15 converted to absolute values. See below for details about the other formulas that appear in this example.

Explanation 

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. You can use SUM instead, but you'll need to enter with control + shift + enter unless you are using Excel 365 where array behavior is native and no special treatment is necessary.

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 as 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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.