Sum of multiple Index Match returns!?

Hi.

Yes - that really isn't a very good layout at all, I'm afraid. All those merged cells!

=SUMIFS(INDEX(F!$1:$1048576,MATCH(K$1,F!A:A,0)+1,),F!$3:$3,G!A2)

Regards

XOR LX... Much simpler than mine (which was considerably more involved). However, I think that this modification is useful...

=SUMIFS(INDEX(F!$1:$1048576,MATCH(OFFSET($K$1,INT((ROW()-2)/34)*34,0),F!A:A,0)+1,),F!$3:$3,G!A70)

@Glenn

Ah. I've see you've attempted to confront the merged cells issue head-on! Respect!

But is it actually necessary here?

Also, I think ROWS is a more rigorous choice than ROW (especially in its unqualified form, i.e. ROW()):

http://excelxor.com/2014/08/25/row-v...er-generation/

Cheers

Yes, I had it working fully. But, since yours was far nicer - I didn't bother posting it. Mine was full of INDIRECTs and ADDRESSs. Just to show that a little knowledge can be a dangerous thing, here's what I had come up with.

Formula:

=SUMIFS(INDIRECT("'F'!"&ADDRESS(MATCH(OFFSET($K$1,INT((ROW()-2)/34)*34,0),F!A:A,0)+1,2)&":"&ADDRESS(MATCH(OFFSET($K$1,INT((ROW()-2)/34)*34,0),F!A:A,0)+1,15)),F!$B$3:$O$3,G!A2)

Of course, I do agree with you about ROWS as opposed to row.

=SUMIFS(INDEX(F!$1:$1048576,MATCH(OFFSET($K$1,INT((ROWS($1:1)-2)/34)*34,0),F!A:A,0)+1,),F!$3:$3,G!A138)

would therefore be better.

As to necessity, surely it is... The Table begins again for the next asset at row 36 and is looking to match the asset number at K35. And it keeps repeating at the same interval down the sheet. Your formula, if copied unmodified into the (ghastly) merged I36 cell, will still (incorrectly) refer to K1.

Ah! Wow! I thought the layout was bad - I didn't realise it was that bad!! I didn't even see the tables below!

Ok, then if the OP is after a single formula to be copied down then that's what you've managed to create, so well done.

Can't help thinking just how difficult some people make it for themselves, though...

Cheers

Yea... whoever came up with the merged cells should be given a serious talking-to (to say the least). Talk about making a simple job more difficult.