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!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies