+ Reply to Thread
Results 1 to 29 of 29

Difficult COUNT IF problem (sequence based on date)

  1. #1
    Registered User
    Join Date
    09-14-2016
    Location
    Tirol
    MS-Off Ver
    Excel 2013
    Posts
    28

    Difficult COUNT IF problem (sequence based on date)

    Hey there!



    What I would like to have is to categorize different events on a specific day. Besides many options there can be a 'R'. If there is a 'R' and the date is (Today), Excel should look for the streak how long it is already a 'R'. In this case it would be 4 day. The output '4d' woulde be now shown in the column Time. If the the sequence is interrupted the count would go back to 0.

    I struggled with the formulating the input to get a proper sequence if there is an interruption. The only thing I get are absolute counts.


    Cheers.
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,776

    Re: Difficult COUNT IF problem (sequence based on date)

    Please try at B4
    =IFERROR(1/(1/LOOKUP(9^9,FREQUENCY(IF(C4:LT4="R",COLUMN(C4:LT4)),IF((C4:LT4<>"R")*(C4:LT4<>""),COLUMN(C4:LT4)))))&"d","")

    Press Ctrl+Shift+Enter and drag down
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Difficult COUNT IF problem (sequence based on date)

    Try this array formula, confirmed in B4 then filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-14-2016
    Location
    Tirol
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Difficult COUNT IF problem (sequence based on date)

    Thank you! Works perfectly! Except on thing:

    If the sequence gets interrupted the output should go back to 0.

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Difficult COUNT IF problem (sequence based on date)

    Quote Originally Posted by qwertz87 View Post
    If the sequence gets interrupted the output should go back to 0.
    You have 2 answers, one of them does that.

  6. #6
    Registered User
    Join Date
    09-14-2016
    Location
    Tirol
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Difficult COUNT IF problem (sequence based on date)

    RIGHT! Case closed!

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Difficult COUNT IF problem (sequence based on date)

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  8. #8
    Registered User
    Join Date
    09-14-2016
    Location
    Tirol
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Difficult COUNT IF problem (sequence based on date)

    True! Sorry But I came up with one more think.

    How would look the formula like IF the count would start for R , M and I

    Cheers.

    Patrick

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Difficult COUNT IF problem (sequence based on date)

    You will see "R" in 2 places in the formula, you just need to change those to the letter that you want to use for the count.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you mean that, for example, R in C4, M in D4 and I in E4 should be counted as 3, then it might need a different approach to make it function correctly.

    If that is what you need, I will look into it for you, but will wait for confirmation from you that it is required first.

  10. #10
    Registered User
    Join Date
    09-14-2016
    Location
    Tirol
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Difficult COUNT IF problem (sequence based on date)

    Quote Originally Posted by jason.b75 View Post
    If you mean that, for example, R in C4, M in D4 and I in E4 should be counted as 3, then it might need a different approach to make it function correctly.

    If that is what you need, I will look into it for you, but will wait for confirmation from you that it is required first.
    Exactly. This it what I would need know. Thank you so much in advance already.

  11. #11
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Difficult COUNT IF problem (sequence based on date)

    I think that this will do what you need, array confirmed as before
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you have to change , to ; for the formula to work with your regional settings, then you will most likely need to change the 2 ; already in the formula to \ in order to get the correct results.

  12. #12
    Registered User
    Join Date
    09-14-2016
    Location
    Tirol
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Difficult COUNT IF problem (sequence based on date)

    This time it does not work. Somehow the output shows a blank cell.

  13. #13
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Difficult COUNT IF problem (sequence based on date)

    That would suggest that either, you did not confirm the array correctly by pressing Ctrl Shift Enter, or that you did not change ; to \ (if needed).

  14. #14
    Registered User
    Join Date
    09-14-2016
    Location
    Tirol
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Difficult COUNT IF problem (sequence based on date)

    I used both your suggestions. Unfortunately it doesn't work within my sheet.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,502

    Re: Difficult COUNT IF problem (sequence based on date)

    Define "doesn't work", please - you need to be far more specific.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  16. #16
    Registered User
    Join Date
    09-14-2016
    Location
    Tirol
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Difficult COUNT IF problem (sequence based on date)

    Quote Originally Posted by AliGW View Post
    Define "doesn't work", please - you need to be far more specific.
    Right, I just copied and pasted the formula into the attached workbook. The output is a blank cell. No indicators referring to an error are shown.
    Attached Files Attached Files

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,502

    Re: Difficult COUNT IF problem (sequence based on date)

    OK - so what are you expecting it to return?

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,502

    Re: Difficult COUNT IF problem (sequence based on date)

    The cell references are wrong for starters. You are referencing row 3 in them, but should be looking at row 4.

  19. #19
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Difficult COUNT IF problem (sequence based on date)

    The formula that I provided for you referenced row 4, I don't understand why you changed it to row 3. This is one reason why it failed.
    The other reason is that you did not change ; to \

    You shouldn't need to change this one
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    09-14-2016
    Location
    Tirol
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Difficult COUNT IF problem (sequence based on date)

    For the example I made a copying mistake. Still, I changed ";" to "\" beforehand. The output was a blank cell. The cell should show the uninterrupted sequence of all Rs, Ms and Is. Does the formula work within your sheet? Otherwise I have to may switch the settings to US based Excel to make it work.
    Last edited by AliGW; 07-09-2019 at 11:26 AM. Reason: Please don't quote unnecessarily!

  21. #21
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Difficult COUNT IF problem (sequence based on date)

    Check in this file, when I saved it, the results in B4:B9 are showing as 3,3,4,2,10,1
    It is unlikely that your settings will affect the results.
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    09-14-2016
    Location
    Tirol
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Difficult COUNT IF problem (sequence based on date)

    Works!

    This was the problem:

    Excel demanded ; instead of \.
    Last edited by qwertz87; 07-10-2019 at 02:05 AM.

  23. #23
    Registered User
    Join Date
    09-14-2016
    Location
    Tirol
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Difficult COUNT IF problem (sequence based on date)

    The final and last thing that is not satiefying is that if the sequence of "R" "M" and "I" are broken then the count should go back to 0. I tried to work it out myself but with the 3 inputs I can't make it.

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,502

    Re: Difficult COUNT IF problem (sequence based on date)

    Show us what you tried.

  25. #25
    Registered User
    Join Date
    09-14-2016
    Location
    Tirol
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Difficult COUNT IF problem (sequence based on date)

    Quote Originally Posted by AliGW View Post
    Show us what you tried.
    I have tried to combine the two formulas and sum it into one. I think I've made a logical mistake though. However, this was my solution.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,502

    Re: Difficult COUNT IF problem (sequence based on date)

    Please attach the workbook where you have tried this.

  27. #27
    Registered User
    Join Date
    09-14-2016
    Location
    Tirol
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Difficult COUNT IF problem (sequence based on date)

    I attached the workbook.
    Attached Files Attached Files

  28. #28
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Difficult COUNT IF problem (sequence based on date)

    What results are you expecting in the sample file? Does this one work?
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  29. #29
    Registered User
    Join Date
    09-14-2016
    Location
    Tirol
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Difficult COUNT IF problem (sequence based on date)

    Quote Originally Posted by jason.b75 View Post
    What results are you expecting in the sample file? Does this one work?
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    It works! Thank you so much!

+ 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. Count a sequence based on nominal input
    By qwertz87 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-03-2019, 08:34 AM
  2. Difficult Count if or Sum if problem
    By m_carter in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-02-2019, 06:32 AM
  3. Formula to Sequence Based on Date and Name
    By blens1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-20-2018, 04:03 AM
  4. Sum/Count based on sequence
    By ulisesap in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-29-2015, 04:03 PM
  5. Replies: 4
    Last Post: 02-02-2012, 09:24 AM
  6. Replies: 11
    Last Post: 11-05-2011, 12:34 PM
  7. a difficult date and month calculation problem
    By jolinchew in forum Excel General
    Replies: 9
    Last Post: 10-06-2011, 04:58 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