• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count top 5 occurrences in range given criteria

S

spena129

New Member
Hi Experts,

I am sure this has been solved before, but I just cannot seem to figure it out on my own, nor can I find exactly what I am looking for in the forum.

I am trying to make a simple dashboard with a section that shows the names, number of occurrences, and a sum of values associated with the top 5 individuals in a range. I am trying to set up a filter by date range as well.

I have my data in a table, with one column being the individual's name, another being the date of entry, and another being a $ value for that particular entry Example:

[pre]
Code: 
Name                     Date        Total Amount
Michael Keaton          1/2/12       $ 40000
Christian Bale          2/5/12        150000
Adam West               2/6/12         10000
Val Kilmer              3/8/12         30000
Michael Keaton          3/10/12        60000
Michael Keaton          3/11/12        10000
I am trying to build a formula that, within a date range chosen by the user, counts each name's occurrence in the range, finds the top 5, and sums the values associated with those top 5.  In addition, I plan to modify the formula to find the top 5 summed values within the range and output the names associated (within the date range).  I have tried to combine Large, Sumproduct, Rank and various other functions, but cannot get it right.  It would look something like this (simplified given example above):


User selects date range: start = 1/1/12; end = 4/1/12

Most frequent        Count        Value
Michael Keaton         3          $110000

Highest Value        Count        Value
Christian Bale         1          $150000
[/pre]
Please Help :)


Thanks!
 
First, I'd recommend just setting up a PivotTable. You could use it to filter your date range, and PT's support the use of displaing "Top x" and could give you the Sum and Count of each name.

http://www.contextures.com/excel-pivot-table-filters-top10.html


Would a PT work, or do you really need formulas?
 
Luke,

I have tried a Pivot Table, but I was hoping to be able to have 2 cells set up as date selections for the date range. I am not sure I can do that with a Pivot Table filter, can I?
 
You could do it with a Change_Event macro. Let's say that you've named the two ranges with dates as StartDate and EndDate. Further assume that the PivotTable (whose name is PivotTable1) is on the sheet, with Names in the row field, Dates and in Page/Report Field, and Value in the Data field. Right-click on sheet tab, view code, paste this in.

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [startdate,enddate]) Is Nothing Then Exit Sub
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
For Each xPI In .PivotItems
'Convert a text string into date value
xItem = DateValue(xPI)
'Hides all the values not in our set range
On Error Resume Next
xPI.Visible = (xItem >= [startdate].Value And xItem <= [enddate].Value)
On Error GoTo 0
Next
.EnableMultiplePageItems = True
End With
End Sub
[/pre]
Main thing to take away is that the macro goes through and limits the filter to just the dates that fit within our criteria. Note that things get a little funny if the date range is invalid (start date after end date, for example).
 
php? Therefmineral I will be envious 69225c0b8ed011228f8c2c5taxiingf98228. NPC deputieds Fang Ming in groups debates|disputess 3bwrongs2ee7134e95503cbd7bbb6a5477c no spfueling the children.,ralph lauren pas cher,michael kors outlet,com/outlined|silhouetted.
I not although|whereast. make them well-creatures. I will succeed.B8.mis-books.Rproud|happy articleds:


http://spoi5888.7bdabb759f9479a56045c9a89de9b83ebbeds.net/viewedthperuse.php?tid=59404&pid=67702&sheets=1&accessory|more|surplus=paged%3D1#pid67702
[*]http://www.the-back-row.com/index.php/2011/09/09/
[*]http://www.the-backing-row.com/indexed.php/2012/06/01/


Henceforth will I recognize that each day I am tested by life in like manner. If I persist, if I continue to try,red bottom shoes, if I continue to charge forwarded, I will succeed.Related craftedicles:


Henceforth will I recognize that each day I am tested by life in like manner. If I persist, if I continue to try, if I continue to charge forward, I will succeed.
 
Luke,

Really appreciate your help. I am going to continue to work on a formula for this, just to see what I can come up with. Any thoughts? I have seen the use of arrays to count the MAX occurrences of text: http://chandoo.org/wp/2011/10/31/using-array-formulas-to-find-count/


In the formula, I was hoping to use LARGE instead of MAX to get 1st-5th highest occurrences, them SUMPRODUCT to filter date range and amounts, but as you probably expected, it did not work.


Looking forward to any insight!
 
If you have a list of all the unique names already, that will make things a little easier. With the list of unique names in Table 2, 2nd column would be:

=SUMPRODUCT((DateRange>=StartDate)*(DateRange<=EndDate)*(NameRange=Name))

This gives you the count. Total dollars in 3rd column would be:

=SUMPRODUCT((DateRange>=StartDate)*(DateRange<=EndDate)*(NameRange=Name)*(DollarRange))

4th cclumn will be needed to give the rank per count. Formula is:

=RANK(Table2Column2,NameCount)+ROW()/10000


The last portion is simply to help break ties.

5th column will be similar:

=RANK(Table2Column3,DollarSum)+ROW()/10000


Now, to pull the top 5 for count would be:

=INDEX(Table2Col1,MATCH(LARGE(Table2Col4,ROW(A1)),Table2Col4,0))

=INDEX(Table2Col2,MATCH(LARGE(Table2Col4,ROW(A1)),Table2Col4,0))

=INDEX(Table2Col3,MATCH(LARGE(Table2Col4,ROW(A1)),Table2Col4,0))


Copy down as desired and the ROW function in the LARGE function will let you pull in more. Similar formulas to pull top 5 based on dollar value:

=INDEX(Table2Col1,MATCH(LARGE(Table2Col5,ROW(A1)),Table2Col5,0))

=INDEX(Table2Col2,MATCH(LARGE(Table2Col5,ROW(A1)),Table2Col5,0))

=INDEX(Table2Col3,MATCH(LARGE(Table2Col5,ROW(A1)),Table2Col5,0))
 
Luke,

I have 220 unique names. Does your formulas require that a specific name be entered ((NameRange=Name)), because that would defeat the purpose of my desire to count all names and find the top 5.


I apologize in advance if I misunderstood.


Thanks!
 
Hi Montrey,

I expected the need for helpers. Both people and columns ;) I have used SUMPRODUCT many times before, but my sticking point seems to be counting the names.


Thanks!
 
Well what you could do, is use a =countif for the names within the date range.


Then you could use a top 5 formula from the countif summary.

=INDEX($BJ$4:$BJ$85,MATCH(LARGE($BJ$4:$BJ$85,ROWS($BF$1:BF1)),$BJ$4:$BJ$85,0))
 
Hi Steve,


Yes, my solution required a list of unique names. It actually works by creating an entire helper table (what the PivotTable would normally be doing) and then you can setup your dashboard area by referencing the helper table.


Mostly, my previous post was to just show that it could be done via formulas only. My recommendation is still to go with a PivotTable, as the formula calculations can get intense.
 
Hi, Steve!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Count%20top%205%20occurrences%20in%20range%20given%20criteria%20%28for%20Steve%20at%20chandoo.org%29.xlsx


In cells range K1:L5 you'll find the highest value solution with just array formulas (remember entering them with Ctrl-Shift-Enter instead of simply Enter).


In cells range K7:L11 I'd like to find the most frequent solution with same kinda formulas... but I didn't achieve it yet. If you, please advise... My tries with MODE function required many helper columns and didn't fully work neither.


Hope it helps.


Regards!
 
@Luke M


Hi!


About your CASFFML issue, here are the formulas:

F1: date from (test for >=)

G1: date until (test for <)

J1:J5 :

=FILA() -----> in english: =ROW()

K1:K5 :

=SI.ERROR(INDICE(A$1:A$9&" "&B$1:B$9;SUMAPRODUCTO((SI($D$1:$D$9=K.ESIMO.MAYOR(($D$1:$D$9)*($C$1:$C$9>=$F$1)*($C$1:$C$9<$G$1);$J1);FILA($D$1:$D$9)))));"") -----> in english: =IFERROR(INDEX(A$1:A$9&" "&B$1:B$9,SUMPRODUCT((IF($D$1:$D$9=LARGE(($D$1:$D$9)*($C$1:$C$9>=$F$1)*($C$1:$C$9<$G$1),$J1),ROW($D$1:$D$9))))),"")

L1:L5 :

=SI.ERROR(INDICE(D$1:D$9;SUMAPRODUCTO((SI($D$1:$D$9=K.ESIMO.MAYOR(($D$1:$D$9)*($C$1:$C$9>=$F$1)*($C$1:$C$9<$G$1);$J1);FILA($D$1:$D$9)))));"") -----> in english: =IFERROR(INDEX(D$1:D$9,SUMPRODUCT((IF($D$1:$D$9=LARGE(($D$1:$D$9)*($C$1:$C$9>=$F$1)*($C$1:$C$9<$G$1),$J1),ROW($D$1:$D$9))))),"")

J7:J11 : =FILA()-6 -----> in english: =ROW()-6

K7:K11 : who knows...

L7:L11 : who knows...


Regards!


PS: added these entries in A8:D9 for testing purposes:

-----

[pre]
Code: 
Alfred	Pennyworth	12/31/2011	1000000
Alan	Napier		04/01/2012	2000000
[/pre]
-----


Dates in proper format as in first comment.
 
You must log in or register to reply here.
Back
Top