SOLVED
Home

#REF! Error Using SUMIF

%3CLINGO-SUB%20id%3D%22lingo-sub-455606%22%20slang%3D%22en-US%22%3E%23REF!%20Error%20Using%20SUMIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-455606%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20SUMIF%20to%20total%20weight%20of%20medium%20in%20a%20given%20pipe%20service%20and%20receiving%20a%20%23REF!%20error%20for%20one%20of%20the%20service%20types.%20I%20compared%20the%20formulas%20for%20the%20different%20service%20types%20on%20this%20page%2C%20as%20well%20as%20the%20other%20sheets%20in%20the%20workbook%20which%20contain%20referenced%20data%2C%20and%20everything%20seems%20to%20be%20consistent%20with%20the%20services%20which%20are%20properly%20summing.%20So%20I%20don't%20really%20know%20what%20the%20problem%20is.%20The%20workbook%20is%20not%20my%20original%20creation%20(I%20was%20given%20it%20at%20work%20and%20told%20to%20revise%20it%20with%20new%20services%2C%20pipe%20lengths%2C%20etc.)%2C%20so%20I%20get%20an%20inkling%20that%20there%20is%20some%20setting%20that%20is%20not%20allowing%20me%20to%20use%20SUMIF%20with%20the%20new%20pipe%20service%20(RHS).%20I've%20updated%20all%20the%20formulas%2Fdata%20validation%2Fetc.%20in%20the%20workbook%20to%20work%20properly%20with%20RHS%2C%20and%20they%20all%20work%20correctly%20except%20this%20summary%20using%20SUMIF.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20a%20snippet%20of%20my%20worksheet%20if%20that%20will%20help!%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20there%20is%20anything%20I%20can%20check%2Fdo%20to%20fix%20this%20error.%20I%20am%20by%20no%20means%20an%20Excel%20expert%2C%20but%20I%20can't%20find%20if%20I%20went%20wrong%20anywhere!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-455606%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-455626%22%20slang%3D%22en-US%22%3ERe%3A%20%23REF!%20Error%20Using%20SUMIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-455626%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F321447%22%20target%3D%22_blank%22%3E%4034_AR%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20couple%20more%20snippets%20showing%20more%20detail.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-455634%22%20slang%3D%22en-US%22%3ERe%3A%20%23REF!%20Error%20Using%20SUMIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-455634%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F321447%22%20target%3D%22_blank%22%3E%4034_AR%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-455706%22%20slang%3D%22en-US%22%3ERe%3A%20%23REF!%20Error%20Using%20SUMIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-455706%22%20slang%3D%22en-US%22%3EBetter%20post%20the%20workbook%20itself%20(remove%20sensitive%20information%20please)%2C%20your%20screenshots%20do%20not%20provide%20sufficient%20information%20to%20go%20on.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-455745%22%20slang%3D%22en-US%22%3ERe%3A%20%23REF!%20Error%20Using%20SUMIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-455745%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3EHere%20you%20go%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-455750%22%20slang%3D%22en-US%22%3ERe%3A%20%23REF!%20Error%20Using%20SUMIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-455750%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-455792%22%20slang%3D%22en-US%22%3ERe%3A%20%23REF!%20Error%20Using%20SUMIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-455792%22%20slang%3D%22en-US%22%3ERow%20602%20of%20INVENTORYPIPE%20contians%20three%20cells%20with%20%23REF!%20errors.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-455815%22%20slang%3D%22en-US%22%3ERe%3A%20%23REF!%20Error%20Using%20SUMIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-455815%22%20slang%3D%22en-US%22%3EAnd%20there%20it%20is!%20Thanks.%20Guess%20I%20just%20got%20lost%20in%20the%20data.%20Thanks%20for%20your%20help!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-455870%22%20slang%3D%22en-US%22%3ERe%3A%20%23REF!%20Error%20Using%20SUMIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-455870%22%20slang%3D%22en-US%22%3EYou're%20welcome!%3C%2FLINGO-BODY%3E
34_AR
Occasional Contributor

I am using SUMIF to total weight of medium in a given pipe service and receiving a #REF! error for one of the service types. I compared the formulas for the different service types on this page, as well as the other sheets in the workbook which contain referenced data, and everything seems to be consistent with the services which are properly summing. So I don't really know what the problem is. The workbook is not my original creation (I was given it at work and told to revise it with new services, pipe lengths, etc.), so I get an inkling that there is some setting that is not allowing me to use SUMIF with the new pipe service (RHS). I've updated all the formulas/data validation/etc. in the workbook to work properly with RHS, and they all work correctly except this summary using SUMIF.

 

I've attached a snippet of my worksheet if that will help!

Please let me know if there is anything I can check/do to fix this error. I am by no means an Excel expert, but I can't find if I went wrong anywhere!

8 Replies

@34_AR 

Here's a couple more snippets showing more detail.

Solution
Better post the workbook itself (remove sensitive information please), your screenshots do not provide sufficient information to go on.
Row 602 of INVENTORYPIPE contians three cells with #REF! errors.
And there it is! Thanks. Guess I just got lost in the data. Thanks for your help!
You're welcome!