Sum data from multiple sheets for multiple criteria

[Frank J]

I have a Summation sheet that needs to collect and total data for staff across 26 other sheets. They are all formatted the same and the data needed sits in the same column on all 26. I'm using their Employee ID # as a reference point since it's unique to each employee. I've tried but failed with Index/Match and am not sure how to use Indirect to help pull the data together

The 26 sheets have the data in data Tables uniquely named.

I'm using Excel 2016

 

[Dave Patton]

"I need it to total all the monthly sheets, how will that do it? Seems to be pulling only an individual month"

Some of your information seemed to indicate that you were trying to secure data from each sheet.

An example of the suggestion follows. Try the suggestion and then edit for your actual workbook.

My recommendation follows:

1. open a new workbook
2. build a mini example (you can start with the information below)
see the icon below the F(x)
3. add or replicate the data sheets
4. name your list of sheet names
5. test and review the formula and results
6. then create a mini model similar to your data
7. test and review the edited formula and results

Then try the formula on your actual data

sum-single-criteria-multiple-sheets.xlsx
	A	B
1	Criteria	Result
2	A	450
3	B	125
Summary

Cell Formulas
Range	Formula
B2:B3	B2	=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetNames&"'!$A$2:$A$10"),A2,INDIRECT("'"&SheetNames&"'!$B$2:$B$10")))

Named Ranges
Name	Refers To	Cells
Sheet_Names	=Summary!$I$2:$I$4	B2:B3
SheetNames	=Summary!$I$2:$I$4	B2:B3

sum-single-criteria-multiple-sheets.xlsx
	A	B
1	Category	Amount
2	A	120
3	B	35
4	C	25
5	A	230
6
Sheet1

 

[Frank J]

I figured it out, I had set my named range of tabs to include empty cells for future use. I just changed the range to only cells with tab names in them and it worked just fine.

 

[Dave Patton]

"I figured it out, I had set my named range of tabs to include empty cells for future use. I just changed the range to only cells with tab names in them and it worked just fine."

Very good.

You could try a mini Table for the sheet names.