How to filter date-wise highest value
Closed
srikanthp
Posts
1
Registration date
Friday May 16, 2014
Status
Member
Last seen
May 16, 2014
-
May 16, 2014 at 10:48 AM
RayH - May 16, 2014 at 12:26 PM
RayH - May 16, 2014 at 12:26 PM
Related:
- How to filter date-wise highest value
- School time table class wise and teacher wise software free - Download - Organisation and teamwork
- How to add snapchat filter to existing video - Guide
- Safe search filter - Guide
- Wise memory optimizer - Download - Cleaning and optimization
- Filter unknown senders - Guide
1 response
Take a look at this as it may help you and others with this:
AAA - Distinct list of Dates
{=IFNA(INDEX($A$2:$A$17,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$17),0,0),0)),"")}
BBB - Count of Distinct Dates in list
{=COUNTIF(A2:A17,INDEX($A$2:$A$17,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$17),0,0),0)))}
Note:
This will produce Zeros that could be hidden using an IF function but is not included here for clarity
CCC -
MAX value of Number field against distinct Date
{=IFNA(MAX(IF(A:A=INDEX($A$2:$A$17,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$17),0,0),0)),B:B)),"")}
These are all ARRAY formulas and are entered with CTRL-SHIFT-Enter
Do not type in the { or } symbols these will automatically be produced after pressing Ctrl-Shift-Enter
Date Number AAA BBB CCC
1/3/2014 99 1/3/2014 6 99
1/2/2014 79 1/2/2014 6 93
1/3/2014 40 1/1/2014 4 100
1/3/2014 64 0
1/2/2014 71 0
1/3/2014 78 0
1/3/2014 18 0
1/2/2014 67 0
1/3/2014 72 0
1/2/2014 93 0
1/2/2014 9 0
1/1/2014 100 0
1/1/2014 6 0
1/1/2014 81 0
1/1/2014 97 0
1/2/2014 11 0
AAA - Distinct list of Dates
{=IFNA(INDEX($A$2:$A$17,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$17),0,0),0)),"")}
BBB - Count of Distinct Dates in list
{=COUNTIF(A2:A17,INDEX($A$2:$A$17,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$17),0,0),0)))}
Note:
This will produce Zeros that could be hidden using an IF function but is not included here for clarity
CCC -
MAX value of Number field against distinct Date
{=IFNA(MAX(IF(A:A=INDEX($A$2:$A$17,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$17),0,0),0)),B:B)),"")}
These are all ARRAY formulas and are entered with CTRL-SHIFT-Enter
Do not type in the { or } symbols these will automatically be produced after pressing Ctrl-Shift-Enter
