Count total values across multiple sheets

Question

I have the following data in my Excel file:

Sheet 1

Product      Quantity
Alpha        100
Bravo        200
Charlie      300

Sheet 2

Product      Quantity
Bravo        100
Delta        200

Sheet 3

Product      Quantity
Charlie      100
Delta        200

I want to get all the records across the multiple sheets and come up with an Excel sheet that summarizes the top products:

Summary

Product      Quantity
Charlie      400
Delta        400
Bravo        300
Alpha        100

Any way to do this?

With 2K rep you should know that a question should at least show the formula or code you have tried and where it has failed. — Mark Fitzgerald, May 30 '14 at 12:00
Do you actually need the unique list of Products creating for your summary sheet as well as the totals, or are you ok to create that list yourself, and so just need help getting the totals? — XOR LX, May 30 '14 at 12:27
@XORLX I need a unique list of products. All sheets don't match with the number of records being shown. — abramlimpin, May 30 '14 at 12:31
Are they all at least in the same column in each sheet, e.g. column A? — XOR LX, May 30 '14 at 12:34

score 1 · Answer 1 · answered May 30 '14 at 15:07

Assuming:

1) The data in each tab begins in row 2 of columns A to B, with Product in column A

2) In none of these tabs does the data go beyond row 1000 (change all occurrences in the formulas (and Named Ranges) of this value to a suitably larger number if it does)

3) There are no blank cells in between the data in any of your tabs

4) You are using Excel 2007 or later

then, firstly, list all of your relevant sheet names (excluding the Summary sheet) in a horizontal range in your Summary sheet somewhere, e.g. D1:F1, making sure that you list them precisely as they appear on the tabs.

Secondly, go to Name Manager and define two new names:

Name: Arry1

Refers to: =MMULT(TRANSPOSE(COUNTIFS(INDIRECT("'"&$D$1:$F$1&"'!A2:A1000"),A$2:A2,INDIRECT("'"&$D$1:$F$1&"'!A2:A1000"),"<>")),ROW(INDIRECT("1:"&COUNTA(A$2:A2)))^0)

Name: Arry2

Refers to: =SUBTOTAL(3,INDIRECT("'"&$D$1:$F$1&"'!A2:A1000"))

(Be sure when you create these that they appear exactly as here. Sometimes, if pasted in, you can end up with quotation marks around the formulas, which is not correct.)

In cell A2 of the Summary sheet enter:

=Sheet1!A2

(Or whatever the name of your first sheet is.)

Then, in A3 of the Summary sheet, this array formula**:

=IFERROR(INDEX(INDIRECT("'"&INDEX($D$1:$F$1,MATCH(FALSE,Arry1=TRANSPOSE(Arry2),0))&"'!A2:A1000"),MATCH(0,COUNTIF(A$2:A2,INDIRECT("'"&INDEX($D$1:$F$1,MATCH(FALSE,Arry1=TRANSPOSE(Arry2),0))&"'!A2:A1000")),0)),"")

Copy down until you start to get blanks.

In B2 (non-array):

=IF(A2="","",SUMPRODUCT(SUMIF(INDIRECT("'"&$D$1:$F$1&"'!A:A"),A2,INDIRECT("'"&$D$1:$F$1&"'!B:B"))))

Again, copy down until you start to get blanks.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Edit: rather than have the sheet names in cells, better still is to use a Named Range for those as well, e.g. _Sheets_: ={"Sheet1","Sheet2","Sheet3"} — XOR LX, May 30 '14 at 16:47

score 1 · Answer 2 · answered May 30 '14 at 16:59

Edit: better still than having the sheet names in cells is to have them as another Named Range, e.g. Sheets:

={"Sheet1","Sheet2","Sheet3"}

The formula is then:

=IFERROR(INDEX(INDIRECT("'"&INDEX(Sheets,MATCH(FALSE,Arry1=TRANSPOSE(Arry2),0))&"'!A2:A1000"),MATCH(0,COUNTIF(A$2:A2,INDIRECT("'"&INDEX(Sheets,MATCH(FALSE,Arry1=TRANSPOSE(Arry2),0))&"'!A2:A1000")),0)),"")

Regards

score 0 · Answer 3 · edited May 23 '17 at 12:03

0

Create a PivotTable with multiple consolidation ranges. For an example (but your case is simpler) with images see here.

edited May 23 '17 at 12:03

Community

1
1

answered May 30 '14 at 13:24

pnuts

58,317
11
87
139

Count total values across multiple sheets

3 Answers3