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


--
seanc
------------------------------------------------------------------------
seanc's Profile: http://www.excelforum.com/member.php...o&userid=28769
View this thread: http://www.excelforum.com/showthread...hreadid=484573

frequency formula with criteria
 

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" wrote in
message ...

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

frequency formula with criteria
 

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


--
seanc
------------------------------------------------------------------------
seanc's Profile: http://www.excelforum.com/member.php...o&userid=28769
View this thread: http://www.excelforum.com/showthread...hreadid=484573

frequency formula with criteria
 

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)),RO W(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" wrote in
message ...

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

frequency formula with criteria
 

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

frequency formula with criteria
 

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

frequency formula with criteria
 

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

frequency formula with criteria
 

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

frequency formula with criteria
 

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

--

HTH

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


"seanc" wrote in
message ...

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