SUMPRODUCT problem

L

LeeHarris

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!
 
L

LeeHarris

LeeHarris said:
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....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumproduct Help! 3
Sumproduct giving #Value error 5
Sumproduct Problem 1
Sumproduct formula problem 5
Sumproduct seems off... HELP PLEASE! 3
SUMPRODUCT 3
Sumproduct not working 5
Sumproduct in matrix means 3

Top