# countifs for unique values

#### Hansulet

##### Board Regular
I need a VBA code or a function like countifs but for unique values.

In my file, I introduced COUNTIFS formula but this formula does not count right for unique values.

COUNTIFS calculate the global number but in my file a person can be situated in the report for many times. For example, If I have 6 rows in report but a Insurance number have situated on 2 rows, the COUNTIFS function returns 6 instead of 5.

My columns are the following:
Name, Middle Name, Insurance Number, Town, Street, decision number, value, date and County

I have to calculate the number of unique persons based on date and county.

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi and welcome to MrExcel.

Here is some sample data and example results that I have put together, if you could do the same with your data, then that would increase the chances of you receiving the correct answer.

The following is probably NOT the answer you require, but my attempt of showing you how your data coul be presented!!...........

Excel Workbook
ABCDEFGHIJ
1NameMiddle NameInsurance #TownStreetDecision #ValueDateCounty
2BenAA1234Town1Street1987610001/03/2013County1
3BillBB1235Town2Street298757502/03/2013County2
4BobAA1236Town3Street398745003/03/2013County3
5TimBB1237Town4Street498732501/03/2013County1
6TomAA1238Town5Street598725002/03/2013County2
7TedBB1239Town6Street698717503/03/2013County3
8BobAA1240Town7Street7987010001/03/2013County1
9BillBB1241Town8Street898697502/03/2013County2
10BenAA1242Town9Street998685003/03/2013County3
11BillBB1243Town10Street1098672501/03/2013County1
12BenAA1244Town11Street1198665002/03/2013County2
13BillBB1245Town12Street1298657503/03/2013County3
14
15
16DateCountyList of UniqueCountCountCount
1701/03/2013County1Ben444
18Tim
19Bob
20Bill
21
Sheet6

You can find the above formulas here.....

How to create a unique distinct list where other columns meet two criteria | Get Digital Help - Microsoft Excel resource
Count unique distinct records with a date and column criteria in excel 2007 | Get Digital Help - Microsoft Excel resource

I hope that helps.

Good luck.

Ak

Last edited:

Replies
1
Views
178
Replies
11
Views
139
Replies
12
Views
568
Replies
1
Views
164
Replies
3
Views
421

1,196,360
Messages
6,014,811
Members
441,847
Latest member
hw407

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