Frequency of a unique list with a condition.

[Don McMahone]

# of HU Desired Values
2003 Freq @ 2003 3
2004 Freq @ 2004 2

YEAR HU
2003 1
2003 1
2003 2
2003 3
2003 3
2003 3
2004 4
2004 4
2004 5
2004 5
2004 5

In the above representation of a spreadsheet the "freq @ 200X" is the field I am trying to develop a formula for. In the list there are 3 unique values in the HU#s for 2003 and 2 unique values in 2004. I can get the frequency of the unique values, but I can't figure how to make it conditional of what year it is in. I would appreaciate any help that anyone can provide.

Thanks,

don

 

[Aladin Akyurek]

Book7
	A	B	C	D	E	F
1	YEAR	HU
2	2003	1		2003	3
3	2003	1		2004	2
4	2003	2
5	2003	3
6	2003	3
7	2003	3
8	2004	4
9	2004	4
10	2004	5
11	2004	5
12	2004	5
Sheet1

The array-formula in E2, which is copied down, is...

=COUNTDIFF(IF($A$2:$A$12=D2,$B$2:$B$12))-1

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter. The formula requires morefunc add-in.

 

[Mark W.]

Book1
	A	B	C	D	E	F	G	H
1	YEAR	HU		2003	3
2	2003	1		2004	2
3	2003	1
4	2003	2
5	2003	3
6	2003	3
7	2003	3
8	2004	4
9	2004	4
10	2004	5
11	2004	5
12	2004	5
13
14
15
Sheet8

 

[Don McMahone]

Fantastic response. I've had two suggestions that will both solve my problem within the first 30 minutes.

Thanks so much for your assistance.

don