Excel formula - filtered array for frequency

[JeroenB]

Hi,

I'm struggling to get the following formulas written. Would appreciate any help.

[ol]
[li]Number of unique Accounts per Year; I manage to get the unique Accounts but cannot filter per year... =SUM(IF(FREQUENCY(B2:B100;B2:B100)>0;1;0)) [/li]
[li]Number of unique new Accounts per Year (So Accounts that didn't show up in all _previous_ Years)[/li]
[li]The Revenue these new Accounts accumulated in the year (they started)[/li]
[/ol]

[pre]
A B C
Date Account Revenue
… … …
2012-12-17 10265 793,8
2012-12-17 10265 -476,28
2012-12-20 6030 -187,5
2012-12-20 6030 103,6
2012-12-28 13463 103,6
2013-01-11 13483 647,5
2013-01-29 13483 103,6
2013-02-07 13454 103,6
2013-02-07 9177 673,4
2011-11-25 13464 178,2
2013-04-18 13464 1113,75
… … …
[/pre]

Much appreciated!

 

[SkipVought]

Hi,

Do you require that the solution be formulas from the source data?

For instance, you could build a simple pivot table or cross sum query to display each account once vertically and each year horizontally, and whatever aggregation you want in the data matrix.

From there, a series of calculations could yield the desired results more easily.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

BTW, the answer to your first question, using the FREQUENCY() function is
[tt]
=SUM((IF(FREQUENCY(MATCH(YEAR(Date)&Account,YEAR(Date)&Account,0),MATCH(YEAR(Date)&Account,YEAR(Date)&Account,0))>0,1)))
[/tt]
entering it as an ARRAY formula shift+ctr+enter/

I get 8 using your data.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Oh, yes, I am using Named Ranges, based on you heading values.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]