All kind of confusing there. May I suggest using SUMPRODUCT()

Not sure what you are trying to sum...

=SUMPRODUCT(--(D14:G14=whatever you're comparing),D15:G26) perhaps?

"BorisS" wrote:

which elements, if any, of an hlookup formula, can be used dynamically in an
array? In other words, if I enter an hlookup formula as array (within a sum
function), where can I have a ":" that let's a part of the formula shift?

for example, if I have {=SUM(HLOOKUP(G14-D14:G14+1,$D$26:G27,2,FALSE))},
will it take the D14:G14, and give me the sum of the formula with every
iteration of D14, E14, F14, and G14 in that spot?

I cannot seem to get it to do the above. Am I doing something wrong?