Sum of multiple Index Match returns!?

**Spicey_888** · 04-24-2015, 11:23 PM

Hi All,

I have been trying for ages to solve this problem with Index Match or Vlookup and Hlookup but to no avail.

I wish to find the sum of total performance for each asset for a given day (24hr periods). However, this requires summing multiple matches for my lookup criteria as my raw data is split into Day Shift (DS) and Night Shift (NS) for each given day. (See the attached workbook, Tab G).

Normally this would be a very easy scenario, and possibly still is however I have some limitations. I am not able to add, remove or change any rows of data, thus no helper columns, and I need to be able to copy the formula down for each Assets table as can be seen in 'Tab F'. I am not concerned about having to change the cell reference for the Asset ID manually for each table if that has to be the case (K1 for the first table).

The other issue is that in Tab G, as the asset ID is listed in a merged cell, which starts above the row of data I need to sum, I have an issue looking up that row and I imagine I may have to use the offset function to counter for this. As a last resort I can un-merge these cells and insert the asset Id in the bottom cell so it aligns with the row of data that will be summed except this is a Last Resort. Again see the attached workbook for more information...

Index_Match_Lookup_Multiple Sums.xlsx

Any help or guidance on this problem would be greatly appreciated. Just remember that I am restricted to not being able to add helper collumns/rows into the existing spreadsheet.

Thanks in advance,

Regards,

Spicey_888

**XOR LX** · 04-25-2015, 02:39 AM

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

**Glenn Kennedy** · 04-25-2015, 04:40 AM

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)

**XOR LX** · 04-25-2015, 04:57 AM

@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

**Glenn Kennedy** · 04-25-2015, 05:12 AM

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:

Please Login or Register  to view this content.

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.

**XOR LX** · 04-25-2015, 05:18 AM

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

**Glenn Kennedy** · 04-25-2015, 05:30 AM

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.

Sum of multiple Index Match returns!?

LinkBack

Thread Tools

Rate This Thread

Display

Sum of multiple Index Match returns!?

Re: Sum of multiple Index Match returns!?

Re: Sum of multiple Index Match returns!?

Re: Sum of multiple Index Match returns!?

Re: Sum of multiple Index Match returns!?

Re: Sum of multiple Index Match returns!?

Re: Sum of multiple Index Match returns!?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Index/Match returns #N/A result

[SOLVED] MATCH/INDEX Formula Returns an Error Instead of 0

[SOLVED] I need to Return Multiple Matches While Index(Match) only returns the First one.

Excel 2007 : Index Match returns 0

Solving INDEX/MATCH duplicate returns

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions