SUMIF on noncontiguous ranges

[Excel Chimp]

Please see sample data. My goal is for N5 and down to sum any cost value with the corresponding code (1,2,3...).

I read a bit and tried to create named ranges for the Range and Sum Range; CODE and COST respectively. They are the same size and shape, and thought they should work. However, I'm getting a value error returned in cells N5 and down.

Thanks for any help.

Excel Workbook
	A	B	C	D	F	G	H	I	J	M	N	O	P
4		Cost	Per unit 216	Code		Cost	Allocated cost	Per unit 216	Code		Cost	Per Unit	Code
5	Appliances	7,046	32.62	2	Backflow	800	596	2.76		Personnel	#VALUE!		1
6	Auto Loan	10,117	46.84		Boiler repair	488	363	1.68	2	Repairs & Maintenance	#VALUE!		2
7	Vertical Blinds	1,786	8.27	3	Drainage	75	56	0.26		Make Ready & Redecorating			3
8	Cabinetry	2,267	10.50	3	Extinguishers	1,637	1,219	5.64		Recreational Amenities			4
9										Contract Services			5
Sheet1

Excel 2010

Cell Formulas
Range	Formula
C4	="Per unit "&$C$1
C5	=$B5/$C$1
C6	=$B6/$C$1
C7	=$B7/$C$1
C8	=$B8/$C$1
I4	="Per unit "&$C$1
I5	=H5/$C$1
I6	=H6/$C$1
I7	=H7/$C$1
I8	=H8/$C$1
H5	=G5*($C$1/$I$1)
H6	=G6*($C$1/$I$1)
H7	=G7*($C$1/$I$1)
H8	=G8*($C$1/$I$1)
N5	=SUMIF(CODE,$P5,COST)
N6	=SUMIF(CODE,$P6,COST)

Excel Workbook
Name	Refers To
CODE	=Sheet1!$D$5:$D$46,Sheet1!$J$5:$J$42
COST	=Sheet1!$B$5:$B$46,Sheet1!$H$5:$H$42
Workbook Defined Names

 

[Andrew Poulsom]

SUMIF expects contiguous ranges. Why don't you just add 2 together?

=SUMIF($D$5:$D$46,P5,$B$5:$B$46)+SUMIF($J$5:$J$42,P5,$G$5:$G$42)

 

[Excel Chimp]

Doh! (slaps forehead)
Thanks so much- I work alone and sometimes you need another person to step back and see the obvious. Keep It Simple Stupid, right?