The first problem is how to determine whole numbers.
This can be done with theINT,TRUNC, or MOD functionsas explained in detail here.
You might at first be tempted to use theCOUNTIForCOUNTIFSfunctions.
Instead, we need a way to work with the array directly withBoolean logic.
Next, we need to convert the TRUE and FALSE values to 1s and 0s.
The formula in G6:
Note the only change to the formula is the logical operator.
This time instead of multiplying the Boolean array byshares, we multiply byvalue.
SUM or SUMPRODUCT?
Why are we using SUMPRODUCT and not theSUM function?
It’s a good question.
In Excel 365, SUM willjust work, sinceExcel 365 handles arrays natively.
For more details, seeWhy SUMPRODUCT?
For example, MOD(10,3) = 1.
The result of MOD carries the same sign as the divisor.
SUMPRODUCT Function
The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.
