(1)
The - is a unary minus operator. SUMPRODUCT processes numbers well, but it can't handle logical types such as TRUE or FALSE.
A test such as ($B$5:$B$12=$F5) returns an array of logical values, such as {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}. SUMPRODUCT can't compute this array so we coerce the logical types into number types by performing an arithmetic operation on it.
For each element in the array:
-TRUE becomes -1, so --TRUE becomes 1
-FALSE becomes 0, so --FALSE becomes 0
So for the whole array:
If
($B$5:$B$12=$F5) gives {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}
then
--($B$5:$B$12=$F5) gives
{1;1;1;0;0;0;0;1}
(2)
Yes, of course, but you lose flexibility. The formula would be like:
You'll need to adjust accordingly for each number grouping.
(3)
SUMPRODUCT is an expensive formula when it is used like this. Too many formulas like this and your workbook calculation will become slow.
Additionally, a pivot table will automatically generate a distinct list of categories for you: with the formula solution, if a new category appears then you'll have to manually add it to your summary table, whereas a pivot table will do it automatically. Note that the pivot table can be set up to reference a dynamic named range/table so it will automatically account for changes in the source data size. The same sort of thing is true for the number groupings - if a new number grouping (say 15-20 or whatever) is necessary then you'll have to recognise that and manually add in new formulas; the pivot table would do that automatically.
Finally, people will generally understand the pivot table more easily than the SUMPRODUCT formula. The SUMPRODUCT formula is complicated!
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks