Excel 2007 : Sumif, Index, match formula

**Bebe22** · 03-09-2012, 04:23 PM

Hi all,

I am trying to create a formula that will sum total OH units based on each category in column A (ie summer 1) from my data dump (MF dump) regardless of where the total OH units column is located on the data dump.

My supervisor keeps having me add things to this spreadsheet so I wanted to use sumif, index and match so that it wouldnt matter if he asks for future changes to the spreadsheet.

Please help!

This is al I could come up with
=SUMIF('MF Dump'!$B:$B,'Men''s Fashion'!$A10,(INDEX(MATCH('Men''s Fashion'!$B$5,'MF Dump'!$E:$Y,0),'MF Dump'!$E:$Y,)))

but it doesnt work

BOP Inventory Composition.xlsx

**NBVC** · 03-09-2012, 04:45 PM

Try it like this:

=SUMIF('MF Dump'!$B:$B,'Men''s Fashion'!$A10,INDEX(E:Y,0,MATCH('Men''s Fashion'!$B$5,'MF Dump'!$E$3:$Y$3,0)))

**Bebe22** · 03-13-2012, 12:31 PM

I had to change the index array to look on the data dump tab but other than that it worked perfectly. Thanks a lot !!!!

=SUMIF('MF Dump'!$B:$B,'Men''s Fashion'!$A23,INDEX('MF Dump'!$E:$X,0,MATCH('Men''s Fashion'!D$5,'MF Dump'!$E$3:$Y$3,0)))

**giselle.lobo** · 07-27-2012, 10:20 AM

Hi,

Please can someone help, I think im facing a similar problem.

If I pull the data using the SUMIF, it shows the correct results.

=SUMIF(dump!$A:$A,summary!$A3,dump!$B:$B)Book2.xls

But if I use the Index match, it returns '0' instead of 2.

=SUMIF(dump!$A:$A,summary!$A3,INDEX(dump!A2:D2,0,MATCH(summary!B2,dump!B2:D2,0)))

I need to use the index match as my columns for months would keep changing, as well as the database.

Please help!