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