How can a column be created to display the count of unique values that differ between two consecutive days?

edit

Assuming that the data is sorted by date and that the unique values are in column A, you can use the following formula to calculate the count of unique values that differ between two consecutive days:

=SUM(--(FREQUENCY(IF(A2:A1000<>A1:A999,ROW(A2:A1000)),IF(A2:A1000<>A1:A999,ROW(A1:A999)))>0))

This is an array formula, so it must be entered with Ctrl+Shift+Enter, not just Enter.

Explanation of the formula:

1. The IF function checks whether the values in column A of the current row and the previous row are different (i.e., whether there has been a change in the unique values).

2. The ROW function returns the row numbers of the corresponding cells.

3. The FREQUENCY function calculates how many times each row number appears in the two arrays.

4. The >0 operator returns an array of TRUE and FALSE values indicating whether each row number appears at least once in the two arrays.

5. The double negative (--) converts the TRUE and FALSE values to 1s and 0s.

6. The SUM function adds up the 1s, which gives the count of unique values that differ between two consecutive days.

Note that the range A2:A1000 and A1:A999 should be adjusted to cover all the cells with data in your worksheet.