How to Count for each category

Please see attached. I did it using a pivot table and by grouping the numbers in the column fields.

Hi Aaron,

The formula solution is second best to a pivot table, but sure - you can do this in XL 2003 (you attached an XLS file) using SUMPRODUCT.

For ease, I put the lower/upper limit for each category in a spare cell.

=SUMPRODUCT(--($B$5:$B$12=$F5),--($C$5:$C$12>=G$1),--($C$5:$C$12<=G$2))

If you can use solely an XLSX file then use SUMIFS instead of SUMPRODUCT - it calculates more efficiently.

(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:

=SUMPRODUCT(--($B$5:$B$12=$F5),--($C$5:$C$12>=1),--($C$5:$C$12<=5))

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!

Hi Aaron,

If I understand correctly, the formula you want in I4 would be:

=SUMPRODUCT(($B$4:$B$11=$H4)*($C$4:$E$11>=1)*($C$4:$E$11<=5))

Hi Aaron,

but I noticed you removed the (--)?

That's right. In this case the arrays have different dimensions so we have to multiply them together (a requirement of SUMPRODUCT). Multiplication itself coerces the logicals to numbers, so there's no need to coerce them to numbers with -- too.

Bob Phillips has a good SUMPRODUCT page which you might find interesting:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html