Solved

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

Posted on 2011-09-12
Medium Priority
251 Views
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).

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.

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
Question by:activematx
• 4
• 2
• 2

LVL 14

Expert Comment

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

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

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

LVL 9

Author Comment

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

ID: 36525970
300954, 300958, 3001383
0

LVL 14

Accepted Solution

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

LVL 9

Author Comment

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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.