excelsumproduct

Index and count in a range formula

I try to create a dynamic SUMPRODUCT, for creating the dynamic I need to use other formula in the SUMPRODUCT. SUMPRODUCT depends from count and match.

Match and count are calculate in G2 and H2. For a dynamic SUMPRODUCT i try the following code

=SUMPRODUCT('Work Center KHK'!"D"&G2:"D"&H2;'Work Center KHK'!"E"&G2:"E"&H2)

It doesn't work, and I don't If it possible solve the problem. Someone can help me?

Solution

  • You need to use =OFFSET(reference, row offset, column offset, height, width)

    =SUMPRODUCT(OFFSET('Work Center KHK'!D1,G2,0,H2,2))

    You may need to adjust the "top-left" corner (used D1 in formula) according to your own setup.