Count Unique Text String

I think you mean "... greater or equal to 0:20 and less then or equal to 0:29 ..."

It might be that you have formatted the cells to show 2 decimal places, and so it would appear that some of the 0.29 values are not being counted. However, the real values might be 0.291 or higher, and so they are outside your range. Applying ROUND(...,2) to column K might help.

Hope this helps.

Pete

Sorry i have corrected title. Thanks to you for correcting me.

Please See the attachment sample file.

Cell C5 is having the issue. the value should be 39. How i will tell you.

If you goto to Master sheet and applied filter and select Dental in column C. Keeping the filter goto column K and apply filter and select 0:20 till 0:29 then you will get the values at the status bar i.e. Bottom Left Corner.

I hope i have explained to you what is my problem.

The status bar shows 37 for me with that filter, which is the same number that your formula results in. Also, filtering just on Dental in column C gives a total of 204 in the status bar, which is also the total in your summary sheet, so I don't see that you are missing any values.

Hope this helps.

Pete

the formula is not calculating last 2 values i.e 0:29 in row no. 538 & 539

Well, it works for me in this sample file - I see that your original formula referred to cells AR5 and AR6, whereas it is I5 and I6 in this file, so presumably your real file is somewhat different to this one. Perhaps that might explain the difference.

Pete

But i have provided you the attached file which is include formula below.

=IF($B4="","",SUMPRODUCT(--(Master!$C:$C="Dental"),--(Master!$K:$K>=I6),--(Master!$K:$K<=I5)))

I just want to know what is the mistake i am doing in the formula and why i am missing 0:29 values which is appearing 2 times in the list.

Look, I applied the filter that you suggested in post #5 and the status bar said there were 37 records. I can see rows 538 and 539 in the displayed list. I put this formula in K555:

=SUBTOTAL(2,K2:K553)

which confirmed there were 37 records. Your SUMPRODUCT formula in the summary sheet also produces a result of 37.

So why do you think that there are 39 ??

Pete

Please check the image below which is showing 39 records.

Total 39.jpg

Well, I don't see that.

I don't think I can help you any further, so I shall unsubscribe from this thread.

Pete