Top 10 with multiple criteria

[MohitMathur1234]

Hi All,

I have some data where I have to find top 10 values but have drop down for year , qtr & Geo as well (with some criteria)
Like for 2016 year, for Q2 qtr, for IND geo...

For more reference Plz find attachment where I have mentioned output as well thur Pivot table.. but I want to do it thur formula.. Please help..!!

 

[Chihiro]

Personally, I'd still go with Pivot and tie dropdown to Pivot Filter using VBA.

If going formula method, I'd suggest building intermediate table that summarize data based on 3 criteria. Then use INDEX, MATCH, LARGE to pull top 10.

 

[bines53]

Hi,

You mean the top 10 IN column Task3 ?

David

 

[MohitMathur1234]

Yes, David - Top 10 in task3 categories based on time.

 

[eFms]

@MohitMathur1234

You need to have a table where subtotals are mentioned and then you can fetch the top 10 from it. Otherwise use pivot table.

 

[bines53]

Hi @MohitMathur1234

The two questions ,

1)Which version of Excel you use ?

2)Your data is always growing each quarter, or they remain around 5000 lines?

Also, some categories you mean, according to the sheet Output, I see only 3, will expand more I can get the whole picture.

David

 

[eFms]

Please see attached sheet.

On sheet2, I have created a table that calculate the "Sum" for Geo, for each year and quarter. Next to it, I have saperated the Data that you are looking for in Column H, and finally the data in sorted using LARGE() to get the desired sorted output. That out is again used to MATCH()+INDEX() the labels from the extreme left of the first table.

The formulas used:
For Table with CSE:

=SUMPRODUCT((Data!$A$2:$A$4982=Sheet2!$B$4982)*(Data!$B$2:$B$4982=Sheet2!C$2)*(Data!$C$2:$C$4982=Sheet2!C$3)*(Data!$E$2:$E$4982=Sheet2!$B4)*(Data!$G$2:$G$4982))

Here is alternate explanation

For List With CSE

=OFFSET(B4,I4,MATCH($H$2&$H$3,$C$2:$F$2&$C$3:$F$3,0))

Using Large to sort:

=LARGE(H4:H31,ROW(A1))

And Index+Match

=INDEX($B$4:$B$31,MATCH(L6,$H$4:$H$31,0))

See attachment as well.

 

[Chihiro]

Here's pivottable method with hidden slicer sheet (requires Excel 2010 and up).

This method allows for each filter (which is actually a PivotTable with filter field only) to be moved around independent of the PivotTable with Top 10 filter applied. Requires no coding and is more efficient than formula method.

PivotFilters can be used to link to other part of dashboard (using named range etc)

 

[MohitMathur1234]

Thank you.... It's awesome

 

[bines53]

Hi @MohitMathur1234

I added helped column,Column of Task3,I gave a number of different,in Column H.

The main formula is,SUMIFS ,You can also use a million lines safely.

David