countifs for unique values

[Hansulet]

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.

 

[Akashwani]

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
	A	B	C	D	E	F	G	H	I	J
1	Name	Middle Name	Insurance #	Town	Street	Decision #	Value	Date	County
2	Ben	AA	1234	Town1	Street1	9876	100	01/03/2013	County1
3	Bill	BB	1235	Town2	Street2	9875	75	02/03/2013	County2
4	Bob	AA	1236	Town3	Street3	9874	50	03/03/2013	County3
5	Tim	BB	1237	Town4	Street4	9873	25	01/03/2013	County1
6	Tom	AA	1238	Town5	Street5	9872	50	02/03/2013	County2
7	Ted	BB	1239	Town6	Street6	9871	75	03/03/2013	County3
8	Bob	AA	1240	Town7	Street7	9870	100	01/03/2013	County1
9	Bill	BB	1241	Town8	Street8	9869	75	02/03/2013	County2
10	Ben	AA	1242	Town9	Street9	9868	50	03/03/2013	County3
11	Bill	BB	1243	Town10	Street10	9867	25	01/03/2013	County1
12	Ben	AA	1244	Town11	Street11	9866	50	02/03/2013	County2
13	Bill	BB	1245	Town12	Street12	9865	75	03/03/2013	County3
14
15
16		Date	County		List of Unique	Count	Count	Count
17		01/03/2013	County1		Ben	4	4	4
18					Tim
19					Bob
20					Bill
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

 

[Akashwani]

**Reply duplicated!!**