tatendamark
01-24-2018, 03:09 AM
I am trying to get the sumproduct of two named ranges, by referencing their headers (see below), but using the concatenate/"&" function. However, I keep getting a value error.
The range on your left (see below) is called "income1" and the one on your right is called "interestA". There are quite a few of these, from 1 to 10, and A to J.
This works: SUMPRODUCT(income1,InterestA)
But this doesn't work: SUMPRODUCT("income"&B3,"Interest"&F3). Cell B3 is the header "1" of the income range "income1", and cell F3 is the header "A" of the interest range "InterestA".
How do I get the latter to work? Example file attached.
1
A
1041
0.820863
2987
0.065637
6112
0.803949
1587
0.879469
6953
0.040989
5071
0.080362
8413
0.520205
4111
0.639688
4284
0.512224
2333
0.471039
2325
0.28264
The range on your left (see below) is called "income1" and the one on your right is called "interestA". There are quite a few of these, from 1 to 10, and A to J.
This works: SUMPRODUCT(income1,InterestA)
But this doesn't work: SUMPRODUCT("income"&B3,"Interest"&F3). Cell B3 is the header "1" of the income range "income1", and cell F3 is the header "A" of the interest range "InterestA".
How do I get the latter to work? Example file attached.
1
A
1041
0.820863
2987
0.065637
6112
0.803949
1587
0.879469
6953
0.040989
5071
0.080362
8413
0.520205
4111
0.639688
4284
0.512224
2333
0.471039
2325
0.28264