Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel - How come my columns don't add up the same?  Need to find missing values.

Posted on 2011-09-12
8
Medium Priority
?
249 Views
Last Modified: 2012-05-12
Hi Excel Experts,

I have attached the spreadsheet here:    Combined-Inventory.xlsx

Inside you will see several sheets (I am only referring to the combined and havo sheets for this question).

 Sheet 1 (Combined)
Looking at column F you will see that I have a SUM there at the bottom.  It is different from my SUM on the other sheet.

 Sheet 2 (HAVO)
I don't know why the SUM's are different.  Basically, I need to find the ITEM ID (PLU) that isn't being counted in the combined sheet.  Why do I have a variance?

Can someone help me locate why the two sheets SUMS are different?
0
Comment
Question by:activematx
  • 4
  • 2
  • 2
8 Comments
 
LVL 14

Expert Comment

by:JP
ID: 36525567
It appears that there are values that are not in the Combined Sheet that exist on the HAVO sheet. If you do a Countif for the range you have 519 item on Combined and 522 on HAVO that are greater than 0.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36525616
You have numbers in column B of HAVO sheet which are included in the sum, even where the description in column C is #N/A - if you exclude those values from the SUM you get the same total in both sheets, i.e. change the formula in HAVO!B524 to this

=SUMIF(C1:C523,"<>#N/A",B1:B523)

regards, barry
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36525631
....alternatively you could do it the other way round. Include the PLU numbers from the three #N/A descriptions in the Combined sheet and then that total will equal the original HAVO total......

regards, barry
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 9

Author Comment

by:activematx
ID: 36525861
It appears that there are values that are not in the Combined Sheet that exist on the HAVO sheet. If you do a Countif for the range you have 519 item on Combined and 522 on HAVO that are greater than 0.

Exactly!  How do I find those 3 numbers which aren't on the combined sheet?  
0
 
LVL 14

Expert Comment

by:JP
ID: 36525970
300954, 300958, 3001383
0
 
LVL 14

Accepted Solution

by:
JP earned 2000 total points
ID: 36525972
Used =IF(COUNTIF(Combined!B:B,HAVO!A1)=1,"YES","NO")
0
 
LVL 9

Author Comment

by:activematx
ID: 36526114
Used =IF(COUNTIF(Combined!B:B,HAVO!A1)=1,"YES","NO")

Thanks, where do I put this formula?
0
 
LVL 14

Expert Comment

by:JP
ID: 36526139
You can place it anywhere on sheet havo in row 1 and copy down then look for the no's
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question