+ Reply to Thread
Results 1 to 15 of 15

Max Occurrences of text within a date range....

  1. #1
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Thumbs up Max Occurrences of text within a date range....

    Hi Folks,
    I am back with something new...
    Actually I have been successful in doing this...
    http://excel-buzz.blogspot.com/2014/...e-and-its.html
    The file has been attached..

    Now I want to count the occurrence of text instead of numbers..
    For that I have attached the file...

    any help on it would be appreciated...

    Vikas Gautam
    Attached Files Attached Files

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Max Occurrences of text within a date range....

    Hi Vikas,

    Are you trying to do something different here?

    =INDEX(B2:B21,MODE(MATCH(B2:B21,B2:B21,0)))

    would be my approach.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Max Occurrences of text within a date range....

    Nice answer... XOR...

    But what about the Date_Ranges...

    Vikas Gautam

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Max Occurrences of text within a date range....

    Well, using your COUNTIFS technique plus array-entry:

    =INDEX(B2:B21,MODE(IF(COUNTIFS(E1,"<="&A2:A21,E2,">="&A2:A21),MATCH(B2:B21,B2:B21,0))))

    Cheers

  5. #5
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Max Occurrences of text within a date range....

    Excellent XOR...
    what if.. A String repeats same no. of times in the date range.. how will we refer that.. as a result..

    If your solved it.. your construction will be best..

    Vikas Gautam

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Max Occurrences of text within a date range....

    What do you mean? What should the result be if there are more than one? You want to list them all in different cells using some sort of INDEX(SMALL set-up?

    Cheers

  7. #7
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Max Occurrences of text within a date range....

    okay in that case we will use Mode.mult..
    fantastic solution xor...

    but what about the Number of occurrences.. ie. second formula..
    do you have any construction for that...?

    Vikas Gautam

  8. #8
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Max Occurrences of text within a date range....

    We could use the standard MAX(FREQUENCY(IF... construction, but this array formula is also a nice alternative:

    =MAX(COUNTIFS(A2:A21,">="&E1,A2:A21,"<="&E2,B2:B21,B2:B21))

    or even:

    =MAX(MMULT(COUNTIFS(A2:A21,">="&E1,A2:A21,"<="&E2,B2:B21,B2:B21),1))

    if you want to avoid CSE.

    Cheers

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Max Occurrences of text within a date range....

    How about this simplified formula for max occurrences.

    =INDEX(B2:B21,DAY(MEDIAN(E1:E2)))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Max Occurrences of text within a date range....

    Quote Originally Posted by AlKey View Post
    How about this simplified formula for max occurrences.

    =INDEX(B2:B21,DAY(MEDIAN(E1:E2)))
    Hi Alkey.

    Certainly looks nice, but how does it work?

    If I make C2:C11 all 2, so that there are 10 of them, your formula still gives 4.

    Cheers

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Max Occurrences of text within a date range....

    Quote Originally Posted by XOR LX View Post
    Hi Alkey.

    Certainly looks nice, but how does it work?

    If I make C2:C11 all 2, so that there are 10 of them, your formula still gives 4.

    Cheers
    Not sure what you mean in C2:C11 The formula is for Max. Occurred Value between two dates.

  12. #12
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Max Occurrences of text within a date range....

    Quote Originally Posted by AlKey View Post
    Not sure what you mean in C2:C11 The formula is for Max. Occurred Value between two dates.
    Sorry - meant B2:B11.

    Regards

  13. #13
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Max Occurrences of text within a date range....

    Quote Originally Posted by XOR LX View Post
    We could use the standard MAX(FREQUENCY(IF... construction, but this array formula is also a nice alternative:

    =MAX(COUNTIFS(A2:A21,">="&E1,A2:A21,"<="&E2,B2:B21,B2:B21))

    or even:

    =MAX(MMULT(COUNTIFS(A2:A21,">="&E1,A2:A21,"<="&E2,B2:B21,B2:B21),1))

    if you want to avoid CSE.

    Cheers
    Sorry XOR but formulas are not performing well..
    Check FORMULA AUDITING steps..

    Don't forget to click *

  14. #14
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Max Occurrences of text within a date range....

    Work fine for me.

    Perhaps you can re-attach a workbook showing me where they fail?

    Regards

  15. #15
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Max Occurrences of text within a date range....

    ok..
    A strange behavior of countifs ... but
    it is providing correct result..
    thanks..

    Can you explain how its working...?
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    part..

    Vikas Gautam

+ 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. [SOLVED] Count occurrences of text in a range
    By braydon16 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-18-2013, 08:33 PM
  2. [SOLVED] Need Help: how to count occurrences of a text character in a range
    By leemanns in forum Excel General
    Replies: 7
    Last Post: 05-05-2012, 01:51 PM
  3. [SOLVED] adding occurrences for date range
    By Mike in forum Excel General
    Replies: 1
    Last Post: 04-28-2005, 05:06 PM
  4. [SOLVED] adding occurrences for date range
    By Mike in forum Excel General
    Replies: 0
    Last Post: 04-11-2005, 01:06 PM
  5. [SOLVED] adding occurrences for date range
    By Mike in forum Excel General
    Replies: 1
    Last Post: 04-08-2005, 08:10 PM

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