Frequency on filtered array

**gr1980** · 12-29-2013, 08:25 AM

Hi,

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

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))
Number of unique new Accounts per Year (So Accounts that didn't show up in all _previous_ Years)
The Revenue these new Accounts accumulated in the year (they started)

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

Much appreciated!

**Tony Valko** · 12-29-2013, 10:36 AM

Try this for the number of unique accounts per year...

Data Range

	A	B	C	D	E	F
1	Date	Account	Revenue	-----	Year	Unique count
2	12/17/2012	10265	793.8		2011	1
3	12/17/2012	10265	-476.28		2012	3
4	12/20/2012	6030	-187.5		2013	4
5	12/20/2012	6030	103.6
6	12/28/2012	13463	103.6
7	1/11/2013	13483	647.5
8	1/29/2013	13483	103.6
9	2/7/2013	13454	103.6
10	2/7/2013	9177	673.4
11	11/25/2011	13464	178.2
12	4/18/2013	13464	1113.75

This array formula** entered in F2 and copied down:

=SUM(IF(FREQUENCY(IF(YEAR(A$2:A$12)=E2,B$2:B$12),B$2:B$12),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

I'm not sure what you want for 2 and 3. Tell us/show us what results you expect for 2 and 3.

**gr1980** · 12-29-2013, 11:02 AM

Many thanks. Works perfect!

Agree. questions 2 and 3 aren't very clear.

On 2: Builds on the results of 1. I would like to know the number of unique Accounts in a certain Year that are below and above a predefined limit. E.g. the number of unique Accounts in 2013 that are below 14000, but above 13000.

On 3: Builds on the resuklts of 2. I would like to know the total Revenue that corresponds with the accounts resulting from 2 (so, per year).

**Tony Valko** · 12-29-2013, 11:21 AM

Maybe these...

Both are array formulas**.

Unique accounts in year 2013 between 13000 and 14000 (inclusive):

E2 = 2013
F2 = 13000
G2 = 14000

=SUM(IF(FREQUENCY(IF(YEAR(A2:A12)=E2,IF(B2:B12>=F2,IF(B2:B12<=G2,B2:B12))),B2:B12),1))

Total revenue for the above:

=SUM(IF(FREQUENCY(IF(YEAR(A2:A12)=E2,IF(B2:B12>=F2,IF(B2:B12<=G2,B2:B12))),B2:B12),B2:B12))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**gr1980** · 12-30-2013, 07:48 AM

Thanks for the help! Is exactly what I needed!

**Tony Valko** · 12-30-2013, 10:49 AM

You're welcome. Thanks for the feedback!

If your question has been answered please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools>Mark this thread as solved.

Frequency on filtered array

LinkBack

Thread Tools

Rate This Thread

Display

Frequency on filtered array

Re: Frequency on filtered array

Re: Frequency on filtered array

Re: Frequency on filtered array

Re: Frequency on filtered array

Re: Frequency on filtered array

Thread Information

Users Browsing this Thread

Similar Threads

Frequency with Subtotal (counting only filtered numbers)

[SOLVED] Counting Frequency of Filtered Data

XLS Array - Unique Value and Frequency?

Frequency Array and Bin Array-how to set up a frequency report on my project.

[SOLVED] Determine Frequency in Filtered List

Bookmarks

Bookmarks

Posting Permissions