Hi
I have to be able to sum a dated row (row C23:P23) of cells, but each cell within the row will be multiplied by a different 'rate' depending on the date of the line, the 'rate' being in a separate array (A1:N18).
Each cell refers to a different person and therefore a different rate and I have created a feeder cell (B23) which determines which column in the array the 'rate' comes from to keep the formula a bit more manageable.
Below is the formula, which is basically the same Index/Match formula repeated/nested.
My question is: Is there another function that I can use that can reduce the need for all these nested Sums and also to future proof it (the array and the amount of 'sums' is likely to increase in size in time, I don't know if I should be investigating SumProduct a bit more to see if that's the answer - any help would be much appreciated - even if it's just pointing me to the correct formula. Thanks in advance.
=SUM(SUM(INDEX(A1:N18,MATCH(C20,A1:A18,0),B23)*C23),SUM(INDEX(A1:N18,MATCH(D20,A1:A18,0),B23)*D23),SUM(INDEX(A1:N18,MATCH(E20,A1:A18,0),B23)*E23),SUM(INDEX(A1:N18,MATCH(F20,A1:A18,0),B23)*F23),SUM(INDEX(A1:N18,MATCH(G20,A1:A18,0),B23)*G23),SUM(INDEX(A1:N18,MATCH(H20,A1:A18,0),B23)*H23),SUM(INDEX(A1:N18,MATCH(I20,A1:A18,0),B23)*I23),SUM(INDEX(A1:N18,MATCH(J20,A1:A18,0),B23)*J23),SUM(INDEX(A1:N18,MATCH(K20,A1:A18,0),B23)*K23),SUM(INDEX(A1:N18,MATCH(L20,A1:A18,0),B23)*L23),SUM(INDEX(A1:N18,MATCH(M20,A1:A18,0),B23)*M23),SUM(INDEX(A1:N18,MATCH(N20,A1:A18,0),B23)*N23),SUM(INDEX(A1:N18,MATCH(O20,A1:A18,0),B23)*O23),SUM(INDEX(A1:N18,MATCH(P20,A1:A18,0),B23)*P23))
Bookmarks