If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SUMPRODUCT problem
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! |
#2
|
|||
|
|||
SUMPRODUCT problem
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.... |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
sumproduct problem | Stefan | Worksheet Functions | 2 | January 5th, 2006 10:59 AM |
Sumproduct Problem | Andibevan | Worksheet Functions | 4 | August 17th, 2005 09:39 AM |
Problem Retrieving Printer Object Information | Beverly R | Setting Up & Running Reports | 2 | May 16th, 2005 06:02 PM |
SUMPRODUCT Problem | Geoffrey Zhu | Worksheet Functions | 1 | August 10th, 2004 01:33 AM |
Productkey problem when installing office 2003 on network | Stefan Schreurs | Setup, Installing & Configuration | 1 | June 1st, 2004 11:16 PM |