frequency formula with criteria

[seanc]

Hi

I am struggling to make a frequency formula that gives me the count of
unique entries of one column based on the criteria of another column.
for example the worksheet looks like this
Collummn
A B

1 Jan 05
2 Jan 05
2 Jan 05
1 feb 05
2 jan 05
3 feb 05
4 feb 05
4 feb 05

If I type in Jan 05 the result will be 2
and if I type in feb 05 the result will be 3
I just cant suceed in tying the count to the month chosen.

Thanks for any help in advance.
Seanc

 

[Barb Reinhardt]

Without knowing how you determine 2 for Jan 05 and 3 for Feb 05, I don't
know how to assist you. Can you explain that?

 

[seanc]

Hi

It means the occurence of unique numbers in jan 05
is 2 ie no 1 appeared once and 2 appeard three times
but the unique count is only 2 ( the normal count for jan 05 would be 4
)

I hope that helps
thanks
Seanc

 

[Bob Phillips]

Sean,

assuming that your date to test for is in cell C1, this will get the number

=SUM(--(FREQUENCY(IF(B1:B10=C1,MATCH(A1:A10,A1:A10,0)),ROW(INDIRECT("1:"&ROW
S(A1:A10))))>0))

it is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[seanc]

Hi

Thanks for the formula but it returns me an answer of true. It
does not return me the number ( unique count)
What could be the problem.

Thanks

 

[Bob Phillips]

Sean,

I have just re-done it and it works fine, returning 2 for Jan. I tried
messing with it to see if I could get TRUE, and I failed miserably.

Did you do all the things I mentioned in the text?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[RagDyeR]

Try this *array* formula, with the date to lookup entered in C1:

=COUNT(1/FREQUENCY(IF((INDEX(A1:B8,,2)=C21),MATCH(INDEX(A1:B8,,1),INDEX(A1:B
8,,1),0)+CELL("Row",A1:B8)),ROW(A1:B8)))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

message
Hi

Thanks for the formula but it returns me an answer of true. It
does not return me the number ( unique count)
What could be the problem.

Thanks

 

[seanc]

hI

Thank you all very very much.
I have just managed to get it right. There was a small mistake
in the syntax ( Bob's answer )
Rag I am going to try your answer as well.

thanks

 

[Bob Phillips]

What small mistake? As I said, it works here.

--

HTH

RP
(remove nothere from the email address if mailing direct)