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

Explanation

In this example, the goal is to get a count of people that hold shares in whole numbers. For example, Bob holds 100 shares (even), so he should be included in the whole number count, while Cindy holds 50.5 shares, so she should not be included in the whole number count.

The first problem is how to determine whole numbers. This can be done with the INT , TRUNC , or MOD functions as explained in detail here . In this example shown above, we are using the MOD function option:

=MOD(A1,1)=0 // TRUE for whole numbers

Now that we know how to test for a whole number, how can we use this approach to get a count of whole numbers? You might at first be tempted to use the COUNTIF or COUNTIFS functions. However, these functions won’t let you use an array * in place of the range argument, so COUNTIF won’t work:

=COUNTIF(MOD(shares,1),0) // won't work!
  • MOD(shares,1) is technically an array operation that returns an array of values. See this article for more information about limitations in COUNTIF, SUMIF, etc.

Instead, we need a way to work with the array directly with Boolean logic . Boolean logic is a technique for building formulas that take advantage of the fact that TRUE = 1, and FALSE = 0 in math operations. In the example shown, this is what the formula in G5 does:

=SUMPRODUCT(--(MOD(shares,1)=0))

Working from the inside out, we first run all values through the MOD test shown above:

=MOD(shares,1)=0 // test all values

Because there are eleven values in shares (C5:C15), we get eleven results in an array like this:

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

In this array, TRUE values represent a whole number, and FALSE values represent a decimal number. Next, we need to convert the TRUE and FALSE values to 1s and 0s. To do this, we use a double-negative (–):

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

This operation returns a numeric array composed only of 1s and 0s:

{1;0;0;1;1;1;1;0;0;1;1}

This is exactly what we need to count whole numbers. This array is returned directly to the SUMPRODUCT function :

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

With just one array to process, SUMPRODUCT returns the sum of all items in the array, 7, which is the count of whole numbers in the range C5:C15.

Count decimal values

To change the formula to count numbers with decimal values, we only need to change the logical operator in the MOD snippet from an equal sign (=) to the not equal to (<>) operator. The formula in G6:

=SUMPRODUCT(--(MOD(shares,1)<>0)) // returns 4

Note the only change to the formula is the logical operator.

Sum whole number shares

To sum whole numbers only, we need to extend the formula a bit by multiplying the Boolean array explained above by the values in the named range shares. The formula in H5 calculates the total number of shares in the whole number group:

=SUMPRODUCT(--(MOD(shares,1)=0)*shares)

Notice the formula is almost the same as above. The result is that the zero values effectively cancel out the shares in the decimal group:

=SUMPRODUCT(--(MOD(shares,1)=0)*shares)
=SUMPRODUCT({1;0;0;1;1;1;1;0;0;1;1}*{100;50.5;110.75;25;50;75;50;60.25;120.75;100;50})
=SUMPRODUCT({100;0;0;25;50;75;50;0;0;100;50})
=450

Sum whole number share values

To sum the values associated with whole number shares, we need to adjust the formula again. This time instead of multiplying the Boolean array by shares , we multiply by value . The formula in I5 is:

=SUMPRODUCT(--(MOD(shares,1)=0)*value)

The formula is solved in exactly the same way:

=SUMPRODUCT(--(MOD(shares,1)=0)*value)
=SUMPRODUCT({1;0;0;1;1;1;1;0;0;1;1}*{2500;1262.5;2768.75;625;1250;1875;1250;1506.25;3018.75;2500;1250})
=SUMPRODUCT({2500;0;0;625;1250;1875;1250;0;0;2500;1250})
=11250

As above, the zero values in the Boolean array cancel out values for non-whole number shares, and the final result returned by SUMPRODUCT is 11250.

SUM or SUMPRODUCT?

Why are we using SUMPRODUCT and not the SUM function ? It’s a good question.

In older versions of Excel (anything but Excel 365 ) the same formula with SUM works, but must be entered as an array formula with Control + Shift + Enter. In Excel 365, SUM will just work , since Excel 365 handles arrays natively . Using SUMPRODUCT ensures that the formula will work in all versions of Excel without requiring Control + Shift + Enter. For more details, see Why SUMPRODUCT?