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