1

I got a data like this:

I want to count how many days I read continuously without interruption.

I use the FLATTEN and SORT formula to combine the two columns and sort it from least to largest. However, how do I count for the number of continue numbers? How to remove repeated numbers?

2
  • For me it is impossible to give a correct answer with these elements. Attach at least one photo of your sheet (better a link to your file), including the result you would like to obtain
    – Daniele
    Sep 11, 2022 at 5:39
  • from your two examples it does not seem that you want the "count the number of continued numbers" but rather the lowest number of consecutive numbers starting from the highest ... is this what you want?
    – Daniele
    Sep 11, 2022 at 6:32

3 Answers 3

1

To count unique numbers in columns A2:B, ignoring duplicates, use countunique(), like this:

=countunique(A2:B)

2
  • Sorry, I didn't explain my question well, I have edited it.
    – Flyson
    Sep 12, 2022 at 0:28
  • Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your manually entered desired results there. Sep 12, 2022 at 6:07
0

If 4 is the result you would like to get in the example of your photo, then this formula should be fine:

=index(COUNT(if(B3:B-B2:B=1,1,))+1)
1
  • That is what I want. However, I really want is to let it count from the last date. The last date is 12 and not numbers that continue to 12 on the sheet, therefore 1. If I delete the last row, it should show 4, because 3,4,5,6 is continued. And I actually want to use column A instead because the numbers on column B are actually like "01/01/2022 12:00:00pm".
    – Flyson
    Sep 13, 2022 at 17:44
0

I finally find a solution.

First, remove duplicate numbers if there are. UNIQUE(A2:A)

Second, I want to count for continue numbers from the bottom, so sort it in descending order. SORT(UNIQUE(A2:A),1,FALSE)

Third, when sorting in descending order, it will result a blank cell in the first result, if the range you are sorting contain ArrayFormula. To solve this, add a query in front, it will exclude the blank cell. QUERY(SORT(UNIQUE(A2:A),1,FALSE),"")

After, I want to find out the difference between each number to the next, if it is 1, then I know they are continuing numbers. MINUS(QUERY(SORT(UNIQUE(A2:A),1,FALSE),""),QUERY(SORT(UNIQUE(A2:A),1,FALSE),"offset 1"))

Of course we are going to use ArrayFormula because we are using a range. ARRAYFORMULA(MINUS(QUERY(SORT(UNIQUE(A2:A),1,FALSE),""),QUERY(SORT(UNIQUE(A2:A),1,FALSE),"offset 1")))

Now, our result should be 6,1,1,1,2. What I'm going to do is to find out the first result that is not 1. This is not directly related to the numbers of continued numbers, however, the number of the first non-one cell is actually giving the same result as what I want. (the amount of 1 is the amount of numbers difference is 1, +1 equals to the amount of continue number, which is also the same as finding the first non-one cell).

So the first thing to do is to separate the one and the non-one result. ARRAYFORMULA(IF(MINUS(QUERY(SORT(UNIQUE(A2:A),1,FALSE),""),QUERY(SORT(UNIQUE(A2:A),1,FALSE),"offset 1"))<>1,"","1"))

Now the result should be [Blank],1,1,1,[Blank]. So, what we are going to do is to find out the first cell equals to blank. MATCH("",ARRAYFORMULA(IF(MINUS(QUERY(SORT(UNIQUE(A2:A),1,FALSE),""),QUERY(SORT(UNIQUE(A2:A),1,FALSE),"offset 1"))<>1,"","1")))

The result is now 1, which is what I want. If I remove the last data (day 12), the result is 4, which is also what I want. This formula will work no matter how many numbers is in column A, we're success.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.