Sumproduct with indirect formula

Vijay_Kumar605 

Does your boss's objection have to do with OFFSET per se, or is it more that the formula (despite the fact that it delivers the desired result) is basically unintelligible to the average (even the above average) user? That is to say, what's the significance of cells H2 and K7 in the grand scheme of things? Where are they coming from, etc.

I find myself wondering whether using Named Ranges--with meaningful names--would make it clearer what's happening, how it's working.

If it were my client, I'd look into changing the array of data on Sheet 2 so that it appeared as more of a database and therefore more amenable to reasonable queries. That's really the heart of your problem (in my humble opinion)....as it forces you into the untenable position of having to use both INDIRECT and OFFSET, each of which is, well, less direct and more abstract than simple straightforward references.

So I'm not giving you a different obscure formula (even if I could, which I couldn't)....I prefer coming at the whole situation for greater clarity and intelligibility, and, in the process, maintainability. I use both INDIRECT and OFFSET in some of my own workbooks. I don't think I've used them together.

But we don't do clients any favors by perpetuating systems that rely on incredibly abstruse formulae, "just because it's possible" and, let's admit it, ego-gratifying to be able to write something like

=SUMPRODUCT((INDIRECT("'"&$C7&"'!"&"$D$1:$D$200")=$D7)*(INDIRECT("'"&$C7&"'!"&"$E$1:$E$200")=$E7),IFERROR((OFFSET(INDIRECT("'"&$C7&"'!"&"j$1:j$200"),F7,INDIRECT("H$"&$K7))/OFFSET(INDIRECT("'"&$C7&"'!"&"I$1:i$200"),7,-4)),0))

and be able to say "but it works."

So my advice is to return to your boss with the suggestion that you re-think the whole process, beginning with the sources of the data......and come up with a more effective and efficient system.