Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]() 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 |
#2
![]() |
|||
|
|||
![]()
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 |
#3
![]() |
|||
|
|||
![]() 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 |
#4
![]() |
|||
|
|||
![]()
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 |
#5
![]() |
|||
|
|||
![]() 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 |
#6
![]() |
|||
|
|||
![]()
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 |
#7
![]() |
|||
|
|||
![]()
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 |
#8
![]() |
|||
|
|||
![]() 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 |
#9
![]() |
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Can a formula be used in the Criteria field of SUMIF?? | Excel Worksheet Functions | |||
Frequency Formula | Excel Discussion (Misc queries) | |||
Average Formula with Criteria | Excel Discussion (Misc queries) | |||
how can I use shading as criteria in a formula? | Excel Worksheet Functions | |||
Countif formula with multiple criteria ie >30 and <60? | Excel Worksheet Functions |