Combining countif and frequency functions to count uniq

Dear Gents,

Could you please help me with the below?.


I'm trying to merge the countif and frequency functions to find the unique values at various times.

For example, I have the time in one sheet, and in another, I have the range time and the column where I would like to count the total unique.

Sheet1 (time)
Time
000000
000500
001000
001500
002000

Sheet2 (database)
19 000000
4 000003
19 000500
19 000501
4 000502
19 000504
1 000505
2 001000
19 001005
3 001010
4 001015
8 001020
20 001500

Desired output

Time
000000 1
000500 2
001000 4
001500 5

I have tried the below formula, but for some reason, it doesn't work, and also, it took a long time as my database has more than 40k rows
=SUMPRODUCT((data!BY:BY=VIBRO!B4)*(ROW(data!BY:BY)-ROW(data!$BY$2)+1=MATCH(data!BY:BY&data!T:T,data!BY:BY&data!T:T,0)))

Appreciate your support

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Dear Caracalla,
The purpose is to check the unique values in the range of time (bins), using <='Sh2'!$B3 seems to don't work. .. Is there any way just to count the values in the range of the time for each time in Sh2.
Thanks a lot for your help.

This proposal employs a lookup table (T2:T5), a helper column and Power Pivot.
1. Cells X2:X5 are populated with 0, 500, 1000 and 1500
2. The helper column (Time Range) is populated using: =AGGREGATE(15,6,T$2:T$5/(T$2:T$5>=Q2),1)
3. Select any cell in columns P:R and from the Insert tab select Pivot Table
4. Select the Add this data to the Data Model check box and then select location for pivot table
5. Drag the Time Range field into the Rows area and Code into the Values area
6. Change the Summarize Values By of the Code field to Distinct Count
Let us know if you have any questions.

Perhaps this tutorial will explain better than I did in post #9: https://www.excelcampus.com/pivot-ta...-pivot-tables/
Scroll down to the section titled "Solution # 2 - Using Power Pivot".
Let us know if you have any questions.

By adding another helper column:
1. The additional helper column (S) is populated using: =COUNTIFS(P$2:P2,P2,R$2:R2,R2)=1
2. Cells Y4:Y7 are populated with the bins
3. Cells Z4:Z7 are populated using: =COUNTIFS(R$2:R$355,Y4,S$2:S$355,TRUE)
Let us know if you have any questions.