OK, thanks for the quick reply to prev. post, it seems a pivot table
might be a better way to do it, but for quickly summing e.g. NFL stats
the sumproduct way seemed quicker.

I tried a version using e.g.

=SUMPRODUCT(($C$2:$C$10000=$R5)*(O$2:O$10000))

this worked on a table of 30 unique players stats and 10 columns but
for some bizarre reason, in the "sacks" column, 2 players are ending up
with a silly fractional number.

e.g. I have Manning three times in the data, twice with "0" as the
source data for sacks, and once with "1". Sum is 1. In my sumproduct
cell shown above, it's coming out as 1.1746324343 etc. I can't see why
this is happening. I've checked and reentered the 0,0 and 1 in the
source data. I've selected and deleted all empty rows up to row 1000,
and I've changed the sumprod range to include only valid data (which
actually only goes to row 79 in this case), and still no joy

I'm just unclear as to why this is happening, or what I'm doing that I
can avoid in future! I assume this is not a bug in sumproduct!

LeeHarris wrote:

OK, thanks for the quick reply to prev. post, it seems a pivot table
might be a better way to do it, but for quickly summing e.g. NFL stats
the sumproduct way seemed quicker.

I tried a version using e.g.

=SUMPRODUCT(($C$2:$C$10000=$R5)*(O$2:O$10000))

this worked on a table of 30 unique players stats and 10 columns but
for some bizarre reason, in the "sacks" column, 2 players are ending up
with a silly fractional number.

e.g. I have Manning three times in the data, twice with "0" as the
source data for sacks, and once with "1". Sum is 1. In my sumproduct
cell shown above, it's coming out as 1.1746324343 etc. I can't see why
this is happening. I've checked and reentered the 0,0 and 1 in the
source data. I've selected and deleted all empty rows up to row 1000,
and I've changed the sumprod range to include only valid data (which
actually only goes to row 79 in this case), and still no joy

I'm just unclear as to why this is happening, or what I'm doing that I
can avoid in future! I assume this is not a bug in sumproduct!


Never mind. For whatever reason, even though I specifically typed in
"0", because of the data format of the cell it wasn't overwriting the
0.174....