# Difficult COUNT IF problem (sequence based on date)

1. ## 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.

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

=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

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

Try this array formula, confirmed in B4 then filled down.
Formula:
`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.

4. ## 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. ## Re: Difficult COUNT IF problem (sequence based on date)

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

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

RIGHT! Case closed!

7. ## 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. ## 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. ## 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:
`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. ## Re: Difficult COUNT IF problem (sequence based on date)

Originally Posted by jason.b75
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. ## Re: Difficult COUNT IF problem (sequence based on date)

I think that this will do what you need, array confirmed as before
Formula:
`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. ## Re: Difficult COUNT IF problem (sequence based on date)

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

13. ## 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. ## Re: Difficult COUNT IF problem (sequence based on date)

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

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

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

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

Originally Posted by AliGW
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.

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

OK - so what are you expecting it to return?

18. ## 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. ## 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:
`Please Login or Register  to view this content.`

20. ## 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.

21. ## 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.

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

Works!

This was the problem:

Excel demanded ; instead of \.

23. ## 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. ## Re: Difficult COUNT IF problem (sequence based on date)

Show us what you tried.

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

Originally Posted by AliGW
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:
`Please Login or Register  to view this content.`

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

Please attach the workbook where you have tried this.

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

I attached the workbook.

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

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

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

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

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