Problem Multiplying 1D Array by 2D array by references of Named Range

[brandondeal]

Hello,

I am trying to solve a problem where I multiply a 1D array of User inputs (Place a 1 in a cell if you want that row of data to be included in a calculation) by a 2D array of calculated data. I'm trying to use Named Ranges throughout the doc and have pasted their sections below. In the first screenshot is where I'm having trouble with my calculation. I assumed that if I used SUMPRODUCT of the named range in the second image below of 1's and blanks that I could use an indexed column from the 2D array shown in the 3rd image.

1st image - issue with calculation

Book1
	B	C	D	E	F	G	H
3	Operating_Expense	0	1	2	3	4	5
4	OM_Base_Fee	#VALUE!
5	Contigency
6	Noncovered_Maintenance
7	Security_Software_Maintenance
Sheet1

Cell Formulas
Range	Formula
C4	C4	=SUMPRODUCT($B4,Compiled_Cost_Schedule*(Compiled_Operating_Years=C$3))

Named Ranges
Name	Refers To	Cells
Compiled_Cost_Schedule	=Sheet1!$O$4:$T$11	C4
Compiled_Operating_Years	=Sheet1!$O$3:$T$3	C4

2nd image - 1D array of Named ranges with 1's and blanks

Book1
	J	K	L	M
3	OM Base Fee	Contigency	Noncovered Maintenance	Security Software Maintenance
4	1
5	1
6		1
7		1
8			1
9			1
10				1
11				1
Sheet1

3rd image - 2D array of calculations

Book1
	O	P	Q	R	S	T
3	0	1	2	3	4	5
4	$ 7,000.00	$ -	$ -	$ -	$ -	$ -
5	$ 7,000.00	$ -	$ -	$ -	$ -	$ -
6	$ -	$ -	$ -	$ -	$ -	$ -
7	$ -	$ -	$ -	$ -	$ -	$ -
8	$ 500.00	$ -	$ -	$ -	$ -	$ 541.22
9	$ 20,000.00	$ -	$ -	$ -	$ -	$ 21,648.64
10	$ -	$ -	$ -	$ -	$ -	$ -
11	$ 15,000.00	$ -	$ -	$ -	$ -	$ -
Sheet1

Named Ranges

Compiled_Cost_Schedule	=Sheet1!$O$4:$T$11
Compiled_Operating_Years	=Sheet1!$O$3:$T$3
Contigency	=Sheet1!$K$4:$K$11
Noncovered_Maintenance	=Sheet1!$L$4:$L$11
OM_Base_Fee	=Sheet1!$J$4:$J$11
Security_Software_Maintenance	=Sheet1!$M$4:$M$11

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUMPRODUCT(INDIRECT($B4)*(Compiled_Operating_Years=C$3),Compiled_Cost_Schedule)

 

[brandondeal]

Hi & welcome to MrExcel.
How about

=SUMPRODUCT(INDIRECT($B4)*(Compiled_Operating_Years=C$3),Compiled_Cost_Schedule)

Y'all make this look too easy on your end. Thank you! Could you help me understand why we would multiply the "Compiled Operating Years = C$3" function by the 1D array, and not the 2D array?

 

[brandondeal]

Y'all make this look too easy on your end. Thank you! Could you help me understand why we would multiply the "Compiled Operating Years = C$3" function by the 1D array, and not the 2D array?

The reason I ask is that I'll be adding additional "triggers" and would be good to understand functionally why we multiply these operators by the respective array (i.e. IF("Name of Inverter"=This,1,0) type of calculation trigger)

 

[Fluff]

You need to multiply the 2 arrays together to find out which rows/columns should be used.