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

@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

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))

@daddylonglegs

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

Cheers

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!!

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

Originally Posted by daddylonglegs

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)

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

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

[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???

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)?