Count unique values based on a condition

[TFTAJLLYMXZP]

Can anyone please suggest how to count the unique values in one range,
say Range A, based on values in a different range, Range B? Range B
consists of dates and I want to count the unique values in Range A for
any particular year.

So far, I've only found the following formula in the MS KB for
counting unique values:

=SUM(1/COUNTIF(RangeA,RangeA))

And, I can count the total values in Range A for one year with:

=COUNT(IF((YEAR(RangeB)=<year>),RangeA))

but how can these be combined is my problem.

Thanks,

Terry

 

[Hoss]

Let's say you have two columns of data like the following:
A B
a 1
a 1
b 1
b 2
c 3
c 2
c 3
c 1
d 5
e 6
f 1
g 8

And you want the unique counts of items in column B based on a
criteria in column A.
so you have

C
a
b
c
d
e
f
g

The following array formula (entered with CTRL-SHIFT-ENTER) should do
the job:
=SUM(IF(FREQUENCY(IF($A$1:$A$12=C1;$B$1:$B$12);$B$1:$B$12)>0;1))

It will give the you following results:
C D
a 1
b 2
c 3
d 1
e 1
f 1
g 1

Which is to be expected, there is only one unique value corresponding
to 'a' (1), and there are two unique values corresponding to
'b' (1,2), while there are three unique values corresponding to
'c' (1,2,3).
I hope that helps.

 

[TFTAJLLYMXZP]

=SUM(IF(FREQUENCY(IF($A$1:$A$12=C1;$B$1:$B$12);$B$1:$B$12)>0;1))

Hoss,

You're brilliant!

For those following along, I have one sheet with column headers in Row
3 equal to years, e.g., 2003, 2004, etc., and rows with various
summary statistics derived from data in other sheets. One of those
sheets has a column (RangeB) with non-unique integers (file numbers in
my case) and a column (RangeA) with a date signifiying when that file
reached a particular milestone. A file could be present on more than
one line with a different date in RangeA, but in the same year. I
wanted to roll up into my summary sheet a count of unique file numbers
for each year.

The magic (array) formula for the 2003 column in my summary sheet is
expressed in my original terms as follows:

{=SUM(IF(FREQUENCY(IF(YEAR(RangeA)=B$3,RangeB),RangeB)>0,1))}

Thanks a ton, Hoss!

Terry

 

[Hoss]

You're welcome. I can't take credit for that though. I had the same
problem recently and found that solution on some website/group
posting.