frequency formula with criteria

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> seanc
> ------------------------------------------------------------------------
> seanc's Profile:
> http://www.excelforum.com/member.php...o&userid=28769
> View this thread: http://www.excelforum.com/showthread...hreadid=484573
>

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> seanc
> ------------------------------------------------------------------------
> seanc's Profile:
http://www.excelforum.com/member.php...o&userid=28769
> View this thread: http://www.excelforum.com/showthread...hreadid=484573
>

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

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)


"seanc" <[email protected]> wrote in
message news:[email protected]...
>
> 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
> ------------------------------------------------------------------------
> seanc's Profile:
http://www.excelforum.com/member.php...o&userid=28769
> View this thread: http://www.excelforum.com/showthread...hreadid=484573
>

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!
=====================================================

"seanc" <[email protected]> wrote in
message news:[email protected]...

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
------------------------------------------------------------------------
seanc's Profile:
http://www.excelforum.com/member.php...o&userid=28769
View this thread: http://www.excelforum.com/showthread...hreadid=484573

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

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

--

HTH

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


"seanc" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> seanc
> ------------------------------------------------------------------------
> seanc's Profile:
http://www.excelforum.com/member.php...o&userid=28769
> View this thread: http://www.excelforum.com/showthread...hreadid=484573
>