# Unique Count (Subscribers) based on Date

1. ## Unique Count (Subscribers) based on Date

Hi Concern,

For instance, Total 45376 records in the sheet. I have got the below first 3 columns from Pivot.

Now i need Unique Numbers column based on Amount and Date.

tried manually calculated from Pivot unique CustomerID for 15th Nov with 90 Amount is 4844 same as 135 amount will follow 523 unique numbers.

How to find the easiest way to calculate all in one shot.

Date Amount Total_Nos Unique_Numbers
15/11/2018 90 6,668.00 4,844.00
15/11/2018 135 889.00 523.00
15/11/2018 225 13,969.00 ?
15/11/2018 450 3,450.00 ?
15/11/2018 900 35.00
15/11/2018 2250 3.00
16/11/2018 90 5,197.00
16/11/2018 135 638.00
16/11/2018 225 11,825.00
16/11/2018 450 2,653.00
16/11/2018 900 42.00
16/11/2018 2250 7.00

Thanks,
Siva

2. ## Re: Unique Count (Subscribers) based on Date

https://stackoverflow.com/questions/...distinct-count

3. ## Re: Unique Count (Subscribers) based on Date

In I2 :
use this formual
Formula:
`Please Login or Register  to view this content.`

This is a CSE formula (Enter formula with CTRL+SHIFT+ENTER)

4. ## Re: Unique Count (Subscribers) based on Date

=SUM(IF(FREQUENCY(IF(\$C\$2:\$C\$45377=G2,IF(\$A\$2:\$A\$45377=F2,\$B\$2:\$B\$45377)),\$B\$2:\$B\$45377)>0,1))

its giving #value

5. ## Re: Unique Count (Subscribers) based on Date

This is a CSE formula (Enter formula with CTRL+SHIFT+ENTER)
After you enter the formula press C+S+E together & you can see {} to close the formual
Have you checked the attached file?

6. ## Re: Unique Count (Subscribers) based on Date

Still getting same.. I do not know whether i am getting rigtht answer here

7. ## Re: Unique Count (Subscribers) based on Date

in J2 I used your formula & you notice {} in the formula. To get that you need to press Ctrl+Shift+Enter
j2 with CSE.PNG

Check here for CSE formula: https://www.ablebits.com/office-addi...nctions-excel/

8. ## Re: Unique Count (Subscribers) based on Date

Thanks, let me try

9. ## Re: Unique Count (Subscribers) based on Date

You save me lot..one thing i noticed CTRL+Shift+Enter its coming all the way first value to all, why this not changing cell by cell..
I did it one by one "C+S+E

10. ## Re: Unique Count (Subscribers) based on Date

no need to do by cell by cell.
You just do for first & dopy+paste or ctdl+D for below cells.

11. ## Re: Unique Count (Subscribers) based on Date

Add rep if you like my assistance...

12. ## Re: Unique Count (Subscribers) based on Date

Thank you

There are currently 1 users browsing this thread. (0 members and 1 guests)