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?

Explanation

We want to count how often items in columns B, C, and D appear together. For example, how often A appears with C, B appears with F, G appears with D, and so on. This would seem like a perfect use of COUNTIFS, but if we try to add criteria looking for 2 items across 3 columns, it isn’t going to work.

A simple workaround is to join all items together in a single cell in a helper column , then use COUNTIFS with a wildcard to count items. We do that with a helper column (E) that joins items in columns B, C, and D using the CONCAT function . The formula in E5, copied down, is:

=CONCAT(B5:D5)

As an alternative, you can also manually concatenate the values like this:

=B5&C5&D5

Because repeated items are not allowed in a combination, the first part of the formula excludes matching items. If the two items are the same, the formula returns a hyphen or dash as text:

=IF($G5=H$4,"-"

If items are different, a COUNTIFS function is run:

COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")

Here, the COUNTIFS function is configured to count “pairs” of items. Only when corresponding values from column G and row 4 appear together in the helper column is the pair counted. Because a letter may appear anywhere, the asterisk (*) wildcard is concatenated to both sides of the value to ensure a match will be counted no matter where it appears in the cell. Note the references to G5 and H4 are mixed references in order to lock the column and row as needed when the formula is copied across the table.