# Count an array's frequency based on another arrays criteria,only counting duplicates once

1. ## Count an array's frequency based on another arrays criteria,only counting duplicates once

Hi Guys,

I need a formula to count the amount of dates whilst only counting the duplicates once. So the formula next to 128809 will = 4, as there are only 4 dates (ie the 07/01/2015 is counted as 1 not for 2). the formula will need to work consequtively down the spread sheet. Next to 202032 the formula should = 1 and next to 201727 the formula should equal 2

Below is the example (Note two columns are being used, A & B):

128809 30/10/2014
128809 29/10/2014
128809 05/11/2014
128809 07/01/2015
128809 07/01/2015
202032 05/03/2015
201727 28/03/2015
201727 29/03/2015
201727 29/03/2015

I have been working with IF, CountIF, Sum, Match and Frequency, but i just cant get it right.

any help is appreciated.

THanks guys :D

2. ## Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

E1=128809

F1

``Please Login or Register  to view this content.``
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

3. ## Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

@Ali Kırksekiz

A good construction, although I don't understand the use of INDEX here. Such a syntax would suggest that you are attempting to avoid array-entry, though of course CSE is still necessary, even with the additional INDEX.

Simply:

=COUNT(1/FREQUENCY(IF(\$A\$1:\$A\$10=E1,IF(\$B\$1:\$B\$10<>"",MATCH(\$B\$1:\$B\$10,\$B\$1:\$B\$10,0))),ROW(\$B\$1:\$B\$10)-ROW(\$B\$1)+1))

is sufficient.

Regards

4. ## Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

For dates (or numbers) you can also use this approach:

=SUM(IF(FREQUENCY(IF(A\$1:A\$10=E1,B\$1:B\$10),B\$1,B\$10),1))

5. ## Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

Yes - I didn't even check what the datatype in question was!

Cheers

6. ## Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

Hi XOR LX

I do agree with you about the fact. It is not necessary INDEX for this formula. Only, ROW formula is enough.

Thank you for the information.

7. ## Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

Hi Guys,

Thank you for your replies :D, Unfortunately these formulas are only returning a value of zero. Also I do not understand the theory of using E1 (E1=128809), as E1 will only represent one number. I have thousands of numbers in Column A...

The data is both general format (Column A) and date format (Column B). For simplicity, lets say the data starts in A1 and B1 and i want to put this formula in C1. In C1 I need the formula to tell me the count of dates (Column B), correlating to the numbers in Column A.

Example Data (My data is thousands of lines long, so this is just a small sample):

128809 30/10/2014
128809 29/10/2014
128809 05/11/2014
128809 07/01/2015
128809 07/01/2015
202032 05/03/2015
201727 28/03/2015
201727 29/03/2015
201727 29/03/2015

In this Example C1-C5 should be 4, C6 should be 1 and C7-C9 should be 2. Remebering that we want to count duplicates only once!

Hope you guys can help out some more. We are on track!!

8. ## Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

If you want to copy down like that you can use this version in C1

=SUM(IF(FREQUENCY(IF(A\$1:A\$1000=A1,B\$1:B\$1000),B\$1:B\$1000),1))

[adjust ranges to cover all your data but don't use whole columns]

confirmed with CTRL+SHIFT+ENTER and copied down

.......but if you do this over thousands of rows then calculation times will be very slow.

If you have the data grouped like that you could get the result just on the top row of each group like this:

First insert a header row if you don't have one then in C2 you can use this formula:

=IF(A2=A1,"",SUM(IF(FREQUENCY(IF(A2:A\$1000=A2,B2:B\$1000),B2:B\$1000),1)))

again confirmed with CTRL+SHIFT+ENTER and copied down

9. ## Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

For dates (or numbers) you can also use this approach:

=SUM(IF(FREQUENCY(IF(A\$1:A\$10=E1,B\$1:B\$10),B\$1,B\$10),1))
DDL, I get "too many arguments" with that?

It highlights B\$10 (and B\$1000)

10. ## Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

Originally Posted by FDibbins
DDL, I get "too many arguments" with that?
Thanks, Ford - I had a comma where it should be a semi-colon, should be

=SUM(IF(FREQUENCY(IF(A\$1:A\$10=E1,B\$1:B\$10),B\$1:B\$10),1))

Now corrected in my last post

11. ## Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

duh, I should have seen that, as well. (Im not as familiar with Freq as I should be)

12. ## Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

[QUOTE=daddylonglegs;4040550]If you want to copy down like that you can use this version in C1

=SUM(IF(FREQUENCY(IF(A\$1:A\$1000=A1,B\$1:B\$1000),B\$1:B\$1000),1))

Hi Daddylonglegs. I tried this formula however it is returning different counts for the same numbers in Column A... Attached is a screen shot.

The formula in Column D needs to represent the date count (COunting duplicates only once in Column L). For each 207606 in column B i want to count all the dates in column L that are also linked to a 207606 in column B (only counting the duplicates once.

So as you can see the first two lines in Column D output 17 but then the third onwards begin to give out different numbers???

13. ## Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

Sorry, I have no idea what causes that - I believe the suggested formula should do what you want, and your amended version seems to be OK - is it the case that there are only 17 instances of 207606 in column B, so the "different date count" result shouldn't be higher than 17? How are you getting the count in column C, is that a COUNTIF formula?

Can you post a small sample or copy of the real data (e.g. 1000 rows, sanitised if necessary)?

14. ## Re: Count an array's frequency based on another arrays criteria,only counting duplicates o

I have just returned from a holiday with the Family. Sorry for the late reply!!

Since being back in the office I have tried your formula again and it seems to be working perfectly fine now?? Not sure what I was doing wrong, possibly a simple error.

Thank you for all your help!

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