Count in the given range with 1 condition duplicate as unique

Hello Friends

Please find the attached excel 2010 file.

I need to values in column B for the given date range by making duplicates as unique.

For the given example the result should be 5 since 72405, 74717, 73478, 79830, 76063.

For the same value date will not vary i.e, even 76063 may repeats 4 times then the date for 76063 will be same for 4 places.

thanks in advance.

what about 73921

Sorry,

The result should be 6 including 73921 please read as `For the given example the result should be 6 since 72405, 74717, 73478, 79830, 76063, 73921.'.

thanks for the qry.

Sorry,

The result should be 6 including 73921 please read as `For the given example the result should be 6 since 72405, 74717, 73478, 79830, 76063, 73921.'.

thanks for the qry.

then try below array formula (Shift+Ctrl+Enter)
=SUM((FREQUENCY(IF($C$2:$C$36>=$E4,IF($C$2:$C$36<=$F4,MATCH($B$2:$B$36,$B$2:$B$36,0))),ROW($B$2:$B$36)-MIN(ROW($B$2:$B$36))+1)>0)+0)

Please Login or Register  to view this content.

Try with in "G4"

Formula:

Please Login or Register  to view this content.

Array formula (SHIFT+CTRL+ENTER)

Try with in "G4"

Formula:

Please Login or Register  to view this content.

Array formula (SHIFT+CTRL+ENTER)

Try this ...

=SUM(IF((C2:C36>=E4)*(C2:C36<=F4),1/COUNTIFS(C2:C36,C2:C36,B2:B36,B2:B36)))

Array formula, enter with Ctrl+Shift+Enter.

Hello Siva, AVK, Phuocam

All of your array formulas working well. Thanks for this formula which is saving my manual calculation work.