Excel non contiguous named range values evaluate to #VALUE, causes SUMIF to fail

[ryanhigs]

All of my named ranges refer to valid cell references, but the range Values come up as null (see Name Manager in photo). This causes the SUMIF function I wish to apply =SUMIF(RangeofID_Codes, ID_Code, RangeOfValues).


I have tried evaluating the criteria against the source (budget codes on bottom left) and they evaluate to TRUE, the ranges have the same number of values (but are non contiguous), but cannot get ranges to check against eachother. I've noticed there are no brackets around the cell references to the non contiguous cells in the RefersTo field for each named range - is this the issue? If so, how do I correct that? Please help, this should be so easy!

 

[FDibbins]

Cannot see your image, so could you give an actual sample of your formula?

 

[ryanhigs]

Thanks for the reply - you can try and see if you can view the image where it is hosted here - http://i.stack.imgur.com/58plH.png

I have a number of named ranges (non contiguous) that I want to query against another non contiguous (but same array size) in order to SUM the first range (budget numbers) only IF the corresponding cell in the other range includes a certain criteria (product code).

Some sample code:

Name:
AprilPayments

Value:
{...}

Refers To:
=Budget_FY!$G$5,Budget_FY!$G$7,Budget_FY!$G$9,Budget_FY!$G$11,Budget_FY!$G$13,Budget_FY!$G$15,Budget_FY!$G$17,Budget_FY!$G$19,Budget_FY!$G$21,Budget_FY!$G$23,Budget_FY!$G$25

Scope:
Workbook

I wish to query this against another named range:

Name:
AprilPaymentsCode

Value:
{...}

Refers To:
=Budget_FY!$G$4,Budget_FY!$G$6,Budget_FY!$G$8,Budget_FY!$G$10,Budget_FY!$G$12,Budget_FY!$G$14,Budget_FY!$G$16,Budget_FY!$G$18,Budget_FY!$G$20,Budget_FY!$G$22,Budget_FY!$G$24

Scope:
Workbook


The formula I wish to apply is =SUMIF(AprilPaymentsCode, A26, AprilPayments) , where A26 is the numeric code designated the budgetary item. I have checked the individual cells in the AprilPaymentsCode range against A26 and they evaluate to TRUE. But for some reason the SUMIF results in #VALUE! error.

Thank you.