Resolved :: Counting unique values based on 2 criteria

pawebb

Board Regular
Joined
Aug 10, 2007
Messages
136
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...
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

kmillen

Board Regular
Joined
Jun 8, 2007
Messages
115
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
ABCDEF
1RMA #RMA Creation DateCreate Month
281618427Jan 03 2007 07:10:24 AMJan-07Jan-072
381627046Jan 09 2007 10:07:11 PMJan-07Feb-072
481627046Jan 09 2007 10:07:11 PMJan-07Mar-071
581678586Feb 15 2007 01:53:07 PMFeb-07
681687626Feb 22 2007 12:48:33 PMFeb-07
781712509Mar 13 2007 04:14:31 AMMar-07
881712509Mar 13 2007 04:14:31 AMMar-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

Board Regular
Joined
Aug 10, 2007
Messages
136
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. :confused:
 

jonlphan

New Member
Joined
Feb 11, 2011
Messages
1
Re: Counting unique values based on 2 criteria

Book1
ABCDEF
1RMA #RMA Creation DateCreate Month
281618427Jan 03 2007 07:10:24 AMJan-07Jan-072
381627046Jan 09 2007 10:07:11 PMJan-07Feb-072
481627046Jan 09 2007 10:07:11 PMJan-07Mar-071
581678586Feb 15 2007 01:53:07 PMFeb-07
681687626Feb 22 2007 12:48:33 PMFeb-07
781712509Mar 13 2007 04:14:31 AMMar-07
881712509Mar 13 2007 04:14:31 AMMar-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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,837
Messages
5,598,378
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top