Sum if with index match

[dolphintabby]

How would I do a sumif function with index match. I have several sheets of data and I want the results to appear on a main table. See example below:


Item number Quantity


Each sheet has an item number with a quantity. Each sheet represents a location. Each location has several items some items are the same at all locations and some are not. I want to match a main data sheet list of items to each sheet then only sum the quantity if the item numbers match.

 

[Luke M]

Since you can't do a 3D SUMIF function, could you setup a PivotTable, combining all the worksheets into one table? This let you create reports very quickly and give you the most versatility.

 

[dolphintabby]

oh I was hoping there was a way to do a match or index match formula so I wouldn't have to do all that copying and pasting data into a conglomerated page from about 10 pages of data.

 

[Luke M]

You don't have to paste all the data into one conglomerated page. A pivottable can pull from multiple sheets.

 

[ssundar]

This site and forum is very much useful and awesome.I have a problem in excel. I have attached on what I am trying to do. Please help me on this.


I have briefed the details below:


The Table on top [ A1 : H8 ] has the student details.

The output should be in 3 sheets [ A, b. and C ] as available in [ A14 to C16 ; E14 to G16 ; I14 to K16 respectively ]


I tried using the array formula as follows , but it worked only for sheet A and not for sheet B and C. I am not sure if there is a mistake from my end or the behaviour is peculiar. Please help me on this.


The formula I use in each sheet [ for 2004 and A (with corresponding links. I have not mentioned the worksheet name as it might be confusing.]


For mark :


=Sumproduct((Index(C3:E8),,Match("A",C2:E2)),N(B3:B8=A15))


For attendance


=Sumproduct((Index(F3:H8),,Match("A",F2:H2)),N(B3:B8=A15))


The actual Data in A1:H8:


Quarter Year Mark Attendance days

A B C A B C

2004Q1 2004 10 30 55 2 5 6

2004Q2 2004 20 20 20 4 8 10

2005Q1 2005 50 80 78 6 2 2

2005Q2 2005 60 10 60 1 2 3

2005Q3 2005 70 70 90 4 5 6

2005Q4 2005 80 25 80 7 8 9


The output should be as:

[ for A in worksheet A]


Year Mark Attendance

2004 30 6

2005 260 18


[ for B in worksheet B]


Year Mark Attendance

2004 50 13

2005 185 17


[ for C in worksheet C]


Year Mark Attendance

2004 75 16

2005 308 20

 

[dolphintabby]

@ Luke M I know it has been a year and I am not sure what happened with my project lol but how do I pull data from several sheets into a single pivot table?


~Dee

 

[Sajan]

Hello,

You could try the following approach for SUMIF across worksheets:


=SUM(SUMIF(INDIRECT(Sheets&"!A1:A50"),A2,INDIRECT(Sheets&"!B1:B50")))

enter with Ctrl + Shift + Enter (instead of Enter)


In this case, "Sheets" is a Name that refers to a range that contains the list of the worksheets.


Cheers,

Sajan.