I have approximately 60 000 rows of product names. Using REMOVE DUPLICATES, I have about 3 000 rows of data. The total units of the 60 000 rows is 349 981 but when I use SUMIF on the unique products, I get 350 067? This does not make sense to me and I would appreciate any insight as to why this could be?
Thanks in advance.
Last edited by Aland2929; 11-17-2013 at 10:23 AM.
It seems indeed strange; but without the file and/or formulas we can't where it goes wrong.
Please post (a part off) the file.
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
Presumably some of the "duplicates" had negative values that have been removed.
You don't tell us which columns you used to do the duplicate comparison, or which columns are being checked and summed.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I column A, I have the product names (60 000 rows). In column B, I have units (total of column B is 349 981). I copied column A to column E and removed the duplicates (3 000 rows). In column F, I used the SUMIF formula (Column A is RANGE, Column E is criteria and Column B is SUM range). There are no minus numbers in column B?
Thanks in advance
How did you get your unique values in column ?
Is the first value realy unique, try this formula to check: =COUNTIF(e1:e60000;F1)
What is the exact 'sumif' - formula?
I used: DATA - REMOVE DUPLICATES to get the unique values. The formula is: SUMIF(A1:A60000,E1,B1:B60000).I have tested column E by using DATA - REMOVE DUPLICATES and no duplicates are found? I am sure the formula is correct but can't figure this out?
Last edited by Aland2929; 11-17-2013 at 10:22 AM.
Did you notice the checkbox 'My data has headers'
Try using this formula:
SUMIF($A$1:$A$60000,E1,$B$1:$B$60000)
If this doesn't help we realy need your xls file
I did change the formula to exclude the headers but still get the same result. I also tried using DATA - ADVANCED - UNIQUE RECORDS ONLY but still get different totals? Unfortunately, I can't attach the data so I will have to continue to try and figure this one out but thanks to all for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks