Link to home
Start Free TrialLog in
Avatar of activematx
activematxFlag for United States of America

asked on

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

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).

 User generated image
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.

 User generated image
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?
Avatar of JP
JP
Flag of United States of America image

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.
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
....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
Avatar of activematx

ASKER

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?  
300954, 300958, 3001383
ASKER CERTIFIED SOLUTION
Avatar of JP
JP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Used =IF(COUNTIF(Combined!B:B,HAVO!A1)=1,"YES","NO")

Thanks, where do I put this formula?
You can place it anywhere on sheet havo in row 1 and copy down then look for the no's