+ Reply to Thread
Results 1 to 4 of 4

assistance with match and rank order formula based on multiple criteria

  1. #1
    Registered User
    Join Date
    02-19-2015
    Location
    Camarillo, Ca
    MS-Off Ver
    Microsoft for MAC 2011
    Posts
    10

    Question assistance with match and rank order formula based on multiple criteria

    Hi Everyone! Since Microsoft took away the "Rank Order" function within pivot tables 2016 for Mac i have been having difficulty finding a way to rank the following data based on unique incident number and date/time values.

    Column A Column B Column C
    12345 5/1/2015 20:20:12 1
    12345 5/1/2015 21:12:08 2
    12346 6/4/2015 17:12:34 1
    12347 7/1:2015 12:00:01 1
    12348 7/10/2015 07:07:12 1
    12348 7/10/2015 07:07:12 1
    12348 7/10/2015 07:20:12 2
    12349 7/10/2015 07:30:45 1

    First I need the incident number in column A to be matched than column B Date/Time values ranked with like values receiving a 1 ranking. Thank you everyone.

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: assistance with match and rank order formula based on multiple criteria

    Try this...

    Data Range
    A
    B
    C
    D
    1
    12345
    5/1/2015
    20:20:12
    1
    2
    12345
    5/1/2015
    21:12:08
    2
    3
    12346
    6/4/2015
    17:12:34
    1
    4
    12347
    7/1/2015
    12:00:01
    1
    5
    12348
    7/10/2015
    7:07:12
    1
    6
    12348
    7/10/2015
    7:07:12
    1
    7
    12348
    7/10/2015
    7:20:12
    2
    8
    12349
    7/10/2015
    7:30:45
    1
    9
    ------
    ------
    ------
    ------


    This array formula** entered in D1 and copied down:

    =SUM(IF(FREQUENCY(IF(A$1:A$8=A1,IF(B$1:B$8=B1,IF(C$1:C$8<C1,C$1:C$8))),C$1:C$8),1))+1

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    02-19-2015
    Location
    Camarillo, Ca
    MS-Off Ver
    Microsoft for MAC 2011
    Posts
    10

    Re: assistance with match and rank order formula based on multiple criteria

    Tony,

    Thank you for the quick reply. The formula works perfectly! Thank you! Sorry for the delay. Its just that my spreadsheet is still calculating and I stopped it to see if the progress! So thank you!

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: assistance with match and rank order formula based on multiple criteria

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Index/Match for multiple criteria duplicate rank
    By dasranch in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-25-2015, 11:40 PM
  2. Index/Match for multiple criteria duplicate rank
    By dasranch in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-25-2015, 11:18 PM
  3. [SOLVED] Index Match Rank with Multiple Criteria
    By Blake 7 in forum Excel General
    Replies: 9
    Last Post: 12-04-2014, 10:44 AM
  4. [SOLVED] Assistance Needed Identifying Min Values Based On Multiple Criteria
    By elderdane in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-25-2014, 05:00 PM
  5. [SOLVED] Need assistance to sum data based on multiple match criteria
    By consulttk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-01-2012, 12:11 PM
  6. [SOLVED] Index/Rank Problem, based on criteria, if match then tiebreaker
    By brotherwo in forum Excel General
    Replies: 4
    Last Post: 08-29-2012, 10:14 AM
  7. Rank maximum based on multiple criteria
    By nasman in forum Excel General
    Replies: 1
    Last Post: 04-09-2012, 12:22 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1