#REF! Error Using SUMIF

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!