Resolved :: Counting unique values based on 2 criteria

[pawebb]

Is it possible to count unique values in one column based on that column and another column? I have a report that has a column of dates (B:B), column of month and year (C:C), and a column of RMA numbers(A:A). The RMA numbers repeat if there is more than one part on the RMA.

I would like to count the number of RMA created each month.
Below should be Jan = 2; Feb = 2; Mar = 1

RMA #|| RMA Creation Date || Create Month
81618427 || Jan 03 2007 07:10:24 AM || Jan 2007
81627046 || Jan 09 2007 10:07:11 PM || Jan 2007
81627046 || Jan 09 2007 10:07:11 PM || Jan 2007
81678586 || Feb 15 2007 01:53:07 PM || Feb 2007
81687626 || Feb 22 2007 12:48:33 PM || Feb 2007
81712509 || Mar 13 2007 04:14:31 AM || Mar 2007
81712509 || Mar 13 2007 04:14:31 AM || Mar 2007

I have a CSE formula that allows me to count based on two criteria but it does not count unique values and you have to specify the two criteria.

Thanks for looking...

 

[kmillen]

Although it's not completely clear to me what your two criteria are, I pasted your example data in cells A1:C8 (the || went away) and entered this CSE formula in cell D1.

=SUM(1/COUNTIF(A2:A8,A2:A8))

It returned 5, the number of unique RMA #s.

 

[Aladin Akyurek]

Re: Counting unique values based on 2 criteria

Is it possible to count unique values in one column based on that column and another column? I have a report that has a column of dates (B:B), column of month and year (C:C), and a column of RMA numbers(A:A). The RMA numbers repeat if there is more than one part on the RMA.

I would like to count the number of RMA created each month.
Below should be Jan = 2; Feb = 2; Mar = 1

RMA #|| RMA Creation Date || Create Month
81618427 || Jan 03 2007 07:10:24 AM || Jan 2007
81627046 || Jan 09 2007 10:07:11 PM || Jan 2007
81627046 || Jan 09 2007 10:07:11 PM || Jan 2007
81678586 || Feb 15 2007 01:53:07 PM || Feb 2007
81687626 || Feb 22 2007 12:48:33 PM || Feb 2007
81712509 || Mar 13 2007 04:14:31 AM || Mar 2007
81712509 || Mar 13 2007 04:14:31 AM || Mar 2007

I have a CSE formula that allows me to count based on two criteria but it does not count unique values and you have to specify the two criteria.

Thanks for looking...

Book1
	A	B	C	D	E	F
1	RMA #	RMA Creation Date	Create Month
2	81618427	Jan 03 2007 07:10:24 AM	Jan-07		Jan-07	2
3	81627046	Jan 09 2007 10:07:11 PM	Jan-07		Feb-07	2
4	81627046	Jan 09 2007 10:07:11 PM	Jan-07		Mar-07	1
5	81678586	Feb 15 2007 01:53:07 PM	Feb-07
6	81687626	Feb 22 2007 12:48:33 PM	Feb-07
7	81712509	Mar 13 2007 04:14:31 AM	Mar-07
8	81712509	Mar 13 2007 04:14:31 AM	Mar-07
9
Sheet1

F2:

Control+shift+enter...

=SUM(IF(FREQUENCY(IF($A$2:$A$8<>"",IF($C$2:$C$8=E2,MATCH("~"&$A$2:$A$8,$A$2:$A$8&"",0))),ROW($A$2:$A$8)-ROW($A$2)+1),1))

and copy down.

 

[pawebb]

Thank you both for your responses.

kmillen - I used the pipes (||) to seperate the data since I did not know how to add an image like Aladin provided. Sorry for the confusion.

Aladin - Thanks for the fomula. It is exactly what I needed.

Do either of you have any recommendations for beefing up my Excel skills? I have the Excel Bible and Excel Power Programming w/ VBA that I am reading through. I am trying to soak up as much as possible.

Thanks Again!

PS - Just found a link explaining how to post teh sheet image.

 

[jonlphan]

Re: Counting unique values based on 2 criteria

Book1
	A	B	C	D	E	F
1	RMA #	RMA Creation Date	Create Month
2	81618427	Jan 03 2007 07:10:24 AM	Jan-07		Jan-07	2
3	81627046	Jan 09 2007 10:07:11 PM	Jan-07		Feb-07	2
4	81627046	Jan 09 2007 10:07:11 PM	Jan-07		Mar-07	1
5	81678586	Feb 15 2007 01:53:07 PM	Feb-07
6	81687626	Feb 22 2007 12:48:33 PM	Feb-07
7	81712509	Mar 13 2007 04:14:31 AM	Mar-07
8	81712509	Mar 13 2007 04:14:31 AM	Mar-07
9
Sheet1

F2:

Control+shift+enter...

=SUM(IF(FREQUENCY(IF($A$2:$A$8<>"",IF($C$2:$C$8=E2,MATCH("~"&$A$2:$A$8,$A$2:$A$8&"",0))),ROW($A$2:$A$8)-ROW($A$2)+1),1))

and copy down.

Aladin,
I have a similar need but when tried to use the provided solution it does not work for me. It appears that Excel expects the column A to be numeric and not text. Can you provide the modified formula where column can be text?
Thank you.