# COUNTIFS for Unique Data

1. ## COUNTIFS for Unique Data

Hi, I'm trying to use a formula to automate finding unique clients in a certain time period.

If Column A is all clients, Column B is dates, I'd like to get the number of unique clients we've done business with in "x" month.

I have this formula to pull only unique values but having trouble incorporating the date piece of it.
{=SUM(IF(1/COUNTIF(A1:A10,A1:A10)=1,1,0))}

I also have this fomrula to pull the distinct values if it's of any help.
{=SUM(IF(B2:B7<>"",1/COUNTIF(A1;A10, A1:A10), 0))}

Any help would be much appreciated!!!

2. ## Re: COUNTIFS for Unique Data

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

3. ## Re: COUNTIFS for Unique Data

Attached. Ideally, I'd like formulas for the top right portion.

4. ## Re: COUNTIFS for Unique Data

G4
=COUNT(1/FREQUENCY(IF(\$C\$4:\$C\$16<=EOMONTH(MID(F4,11,9)&2020,0),MATCH(\$B\$4:\$B\$16,\$B\$4:\$B\$16,)),ROW(\$B\$1:\$B\$16)))
confirm by press Ctrl+Shift+Enter

H4
=G4-N(G3)

G14
=COUNT(1/FREQUENCY(MATCH(\$B\$4:\$B\$16,\$B\$4:\$B\$16,),ROW(\$B\$1:\$B\$16)))

H14
=SUMPRODUCT(--(FREQUENCY(MATCH(\$B\$4:\$B\$16,\$B\$4:\$B\$16,),ROW(\$B\$1:\$B\$16))=1))

FREQUENCY(MATCH()) is calculate a lot faster than COuntifs array.
Please test with 1000 rows of data to compare.

5. ## Re: COUNTIFS for Unique Data

I'm generally a little confused on how FREQUENCY works but I'll look into it. I gave you a somewhat simplified example, which I'm now thinking I shouldn't have haha.

What if there's another field I need to look at (Say we lost a deal, that shouldn't count towards clients) or if I'm only looking for repeat clients between two dates (say for a particular month instead of before X date)

I tried adjusting IF() part of formula to include AND() in the logic, but didn't seem to work. Reattached with an updated example.

Thanks so much for your help!

6. ## Re: COUNTIFS for Unique Data

with attachment

7. ## Re: COUNTIFS for Unique Data

H22
=COUNT(1/FREQUENCY(IF((\$C\$4:\$C\$16>=F22)*(\$C\$4:\$C\$16<G22)*(\$D\$4:\$D\$16="Won"),MATCH(\$B\$4:\$B\$16,\$B\$4:\$B\$16,)),ROW(\$B\$1:\$B\$16)))
Ctrl+Shift+Enter

8. ## Re: COUNTIFS for Unique Data

Originally Posted by Bo_Ry
H22
=COUNT(1/FREQUENCY(IF((\$C\$4:\$C\$16>=F22)*(\$C\$4:\$C\$16<G22)*(\$D\$4:\$D\$16="Won"),MATCH(\$B\$4:\$B\$16,\$B\$4:\$B\$16,)),ROW(\$B\$1:\$B\$16)))
Ctrl+Shift+Enter
You are a legend!!

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1