# Resolved :: Counting unique values based on 2 criteria

#### pawebb

##### Board Regular
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...

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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.

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.

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.

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.
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.

Replies
13
Views
739
Replies
1
Views
74
Replies
2
Views
367
Replies
2
Views
371
Replies
0
Views
108

1,203,521
Messages
6,055,883
Members
444,830
Latest member

### 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.

### Which adblocker are you using?

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

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