I can make a solution using a User Defined Function and named ranges. Its a two step solution, which requires just one formula per step, regardless of how many types of categories you wish to sum.
This is not
exactly what the op wanted so if an
MVP has a better solution please share. But it does actually do the job quite well I think. The advantage of this system is can handle as many worksheets you want and as many field categories as you want. So if you later add more worksheets the UDF will still work (provided you update your named ranges).
The OP might not have a use for this (although I think it does solve his problem), but I learned a lot figuring it out, and will no doubt I will use something like this myself for work problems.
The values are summed on one page (which may be hidden if you require), and then ranked on your summary page.
This file illustrates (I hope people can access this).
https://www.dropbox.com/s/f8pezlfbj8012q2/Excel Question - Test Solution with VBA.xlsm
You need to create a lot of named ranges, each category (Sales, Units etc) * each worksheet (eg Platform 1, Platform 2, Platform 3 etc)
Each is a sample of the named ranges (the p prefix stands for "Platform" as per the OPs sample workbook).
[TABLE="width: 485"]
<tbody>[TR]
[TD]p1.Date[/TD]
[TD]='Platform 1'!$A$2:$A$143[/TD]
[/TR]
[TR]
[TD]p1.Palettes[/TD]
[TD]='Platform 1'!$E$2:$E$143[/TD]
[/TR]
[TR]
[TD]p1.Sales[/TD]
[TD]='Platform 1'!$C$2:$C$143[/TD]
[/TR]
[TR]
[TD]p1.Units[/TD]
[TD]='Platform 1'!$D$2:$D$143[/TD]
[/TR]
[TR]
[TD]p2.Date[/TD]
[TD]='Platform 2'!$A$2:$A$143[/TD]
[/TR]
[TR]
[TD]p2.Palettes[/TD]
[TD]='Platform 2'!$E$2:$E$143[/TD]
[/TR]
[TR]
[TD]p2.Sales[/TD]
[TD]='Platform 2'!$C$2:$C$143
[/TD]
[/TR]
[TR]
[TD]p2.Units[/TD]
[TD]='Platform 2'!$D$2:$D$143[/TD]
[/TR]
</tbody>[/TABLE]
To speed up the process of making the name ranges I have written a macro. This macro needs to reference two other named ranges which I suggest you store on an additional worksheet called "Names"
Create two named ranges to get started "MySheets" and "MyFields". If you don't know how to make named ranges Google it or post back here and I can give you more instructions.
MySheets - which list all of the worksheet names containing the data you wish to aggregate (be careful to get worksheet names exactly right).
MyFields - which lists all of the fields (these must be correct too)
For example:
[TABLE="width: 485"]
<tbody>[TR]
[TD]MyFields[/TD]
[TD]=Names!$B$1:$B$4[/TD]
[/TR]
[TR]
[TD]MySheets[/TD]
[TD]=Names!$A$1:$A$5[/TD]
[/TR]
</tbody>[/TABLE]
This is the macro which creates the names (it won't work until you have created and populated MySheets and MyFields. This is faster than creating them manually.
Code:
Sub CreateNamedRanges()
Dim ws As Worksheet
Dim MyRng As Range, MyRng2 As Range, MyRngC As Range
Dim SheetNames(), FieldNames(), cntSheets As Integer, cntFields As Integer
Dim ColNum As Integer
Dim strName As String
SheetNames = Application.Transpose([MySheets].Value)
FieldNames = Application.Transpose([MyFields].Value)
For cntSheets = LBound(SheetNames) To UBound(SheetNames)
MsgBox SheetNames(cntSheets)
Set ws = Worksheets(SheetNames(cntSheets))
Set MyRng = ws.Range("A1").CurrentRegion
Set MyRng2 = MyRng.Offset(1, 0).Resize(MyRng.Rows.Count - 1)
For cntFields = LBound(FieldNames) To UBound(FieldNames)
On Error Resume Next
ColNum = WorksheetFunction.Match(FieldNames(cntFields), MyRng.Rows(1), 0)
Set MyRngC = MyRng2.Columns(ColNum).Cells
MyRngC.Name = "p" & cntSheets & "." & FieldNames(cntFields)
On Error GoTo 0
Next cntFields
Next cntSheets
End Sub
If you wish to delete the names quickly (for example you want to change the field names) run this macro:
Code:
Sub DeleteFieldNames()
Dim ws As Worksheet
Dim MyRng As Range, MyRng2 As Range, MyRngC As Range, MyCell As Range
Dim SheetNames(), FieldNames(), cntSheets As Integer, cntFields As Integer
Dim ColNum As Integer
Dim strName As String
SheetNames = Application.Transpose([MySheets].Value)
FieldNames = Application.Transpose([MyFields].Value)
For cntSheets = LBound(SheetNames) To UBound(SheetNames)
For cntFields = LBound(FieldNames) To UBound(FieldNames)
On Error Resume Next
strName = "p" & cntSheets & "." & FieldNames(cntFields)
ActiveWorkbook.Names(strName).Delete
On Error GoTo 0
Next cntFields
Next cntSheets
End Sub
And here is the user defined function. This is really just a SUMIF formula that references the named ranges that we have created, but it loops through all the worksheets adding the values of all of the named ranges.
Code:
Function SumSales(DateCell As Range, TypeCell As Range) As Double
Dim DateRng As String
Dim TypeRng As String
Dim SheetsCount As Integer
Dim cnt As Integer
Dim tempSum As Double
SheetsCount = [MySheets].Cells.Count
tempSum = 0
For cnt = 1 To SheetsCount
DateRng = "p" & cnt & "." & "Date"
TypeRng = "p" & cnt & "." & TypeCell
tempSum = WorksheetFunction.SumIf(Range(DateRng), DateCell.Value, Range(TypeRng))
Next cnt
SumSales = tempSum
End Function
Here is a sample of the output - first the totals (this can be on a hidden workheet)
[TABLE="width: 335"]
<tbody>[TR]
[TD]Aggregate[/TD]
[TD]Rank[/TD]
[TD]Rank[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Sales[/TD]
[TD]Units[/TD]
[TD]Palettes[/TD]
[/TR]
[TR]
[TD]06/09/2013[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12/09/2013[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]13/09/2013[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]19/09/2013[/TD]
[TD]4[/TD]
[TD]11[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]21/09/2013[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]24/09/2013[/TD]
[TD]24[/TD]
[TD]41[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]25/09/2013[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]26/09/2013[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]27/09/2013[/TD]
[TD]11[/TD]
[TD]4[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]28/09/2013[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]29/09/2013[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
In the above the formula looks like this: =SumSales($C8,D$7)
The C column contains the dates while the row 7 (starting at column D) contains the category names such as "Sales"
And he is the RANK output
[TABLE="width: 335"]
<tbody>[TR]
[TD]Aggregate[/TD]
[TD]Rank[/TD]
[TD]Rank[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Sales[/TD]
[TD]Units[/TD]
[TD]Palettes[/TD]
[/TR]
[TR]
[TD="align: right"]06/09/2013[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]12/09/2013[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]13/09/2013[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]19/09/2013[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]21/09/2013[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]24/09/2013[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]25/09/2013[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]26/09/2013[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]27/09/2013[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]28/09/2013[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]29/09/2013[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]
And this is what the formula looks like
=RANK('Summary Total'!D8,'Summary Total'!D$8:D$18,0)
Please note if and when you make changes to field names you must re-enter the formula (it is not just enough to press F9 to calculate).
I'll add one more thing. The macro creates static named ranges. When I first started attacked this problem I tried using some dynamic named ranges, which could be a worthwhile alternative:
For example:
p1.Date =OFFSET('Platform 1'!$A$1,0,MATCH("Date",'Platform 1'!$1:$1,0)-1,COUNTA('Platform 1'!$A:$A),1)
p1.Sales =OFFSET('Platform 1'!$A$1,0,MATCH("Sales",'Platform 1'!$1:$1,0)-1,COUNTA('Platform 1'!$A:$A),1)
BUT I think the static names are just as easy. If you add more data just delete the existing names and then recreate them using the macro I have provided.
If any MVP has comments or think this can be improved I would love to hear.
And of course if there is an Excel genious out there who know how to solve the OPs problem with a single formula tell us all about it. I would amazed if anybody could solve with a single formula.
Well now the OP has two solutions he can experiment with. Marcello's might be easier if the OP wants to avoid using VBA. It depends.
