Counting Unique Records

[Chris Waller]

I have an Excel Spreadsheet contains 8,000 rows by 30 columns. I am trying to count the number of National Insurance numbers in column F where column S = FAMC and column T = NP. I am trying to count the occurrence of National Insurance numbers that contain this criteria, however, if the nation insurance number appears multiple times I only want to count the first occurrence. I have tried to filter the data using the information in column S and T, but there are still duplicates displayed. Does anyone know of a formula that would cover this eventuality? TIA.

 

[JoeMo]

I have an Excel Spreadsheet contains 8,000 rows by 30 columns. I am trying to count the number of National Insurance numbers in column F where column S = FAMC and column T = NP. I am trying to count the occurrence of National Insurance numbers that contain this criteria, however, if the nation insurance number appears multiple times I only want to count the first occurrence. I have tried to filter the data using the information in column S and T, but there are still duplicates displayed. Does anyone know of a formula that would cover this eventuality? TIA.

One way to do this would be to make a copy of your sheet (right-click sheet tab>Move or Copy), then use Data>Data Tools> Remove Duplicates checking columns F,S & T. Then use the COUNTIFS function with F, S & T ranges as arguments.

 

[AhoyNC]

I would probably go with Joe's suggestion, but here is a formula that should do what you want.
This is an array formula and must be entered with CTRL-SHIFT-ENTER (command-return on a MAC).

Excel Workbook
	E	F	G	R	S	T	U	V	W
1		Ins. #			Code1	Code2		Count
2		12			FMAC	NP		4
3		15			FMAC	NP
4		12			FMAC	NP
5		56			tyu	NP
6		12			tyu	fg
7		76			FMAC	NP
8		45			FMAC	NP
9		45			FMAC	NP
10		12			FMAC	NP
11
Sheet

 

[Chris Waller]

Joe,

Thanks for the reply. As I am leave this week I will give it a try when I get back to work, but I understand what you are doing so I will give it a whirl.

 

[Chris Waller]

Excel Jeannie,

Thanks for the reply. I am leave this week, so I tried about 17 rows containing data in the columns specified. I also did the CTRL+SHIFT+ENTER, but for some reason it didn't count anything.

 

[AhoyNC]

When you use CTRL-SHIFT_ENTER, Excel should but "{}" around the formula. If you are on a MAC then use COMMAND-RETURN.
Also, check to make sure ranges match your data.

 

[Chris Waller]

When you use CTRL-SHIFT_ENTER, Excel should but "{}" around the formula. If you are on a MAC then use COMMAND-RETURN.
Also, check to make sure ranges match your data.

I have just tried the same formula on 9 lines of data. I also used CTRL-SHIFT-ENTER and it did put the curly brackets around the formula, but the answer was still 0.

 

[Chris Waller]

AhoyNC,

In column F there are labels rather than numbers. Would this make any difference?

 

[AhoyNC]

As you can see in the example below labels would not make a difference.
I'm guessing here, but check to make sure that the data in columns S & T there are no spaces either before or after the FMAC or NP. If it's not finding a match in either or both columns it will return 0.

Excel Workbook
	F	G	Q	R	S	T	U	V
1	Ins. #				Code1	Code2		Count
2	a				FMAC	NP		4
3	b				FMAC	NP
4	a				FMAC	NP
5	c				tyu	NP
6	a				tyu	fg
7	d				FMAC	NP
8	e				FMAC	NP
9	e				FMAC	NP
10	a				FMAC	NP
Sheet

 

[Chris Waller]

As you can see in the example below labels would not make a difference.
I'm guessing here, but check to make sure that the data in columns S & T there are no spaces either before or after the FMAC or NP. If it's not finding a match in either or both columns it will return 0.

Excel Workbook
	F	G	Q	R	S	T	U	V
1	Ins. #	*	*	*	Code1	Code2	*	Count
2	a	*	*	*	FMAC	NP	*	4
3	b	*	*	*	FMAC	NP	*	*
4	a	*	*	*	FMAC	NP	*	*
5	c	*	*	*	tyu	NP	*	*
6	a	*	*	*	tyu	fg	*	*
7	d	*	*	*	FMAC	NP	*	*
8	e	*	*	*	FMAC	NP	*	*
9	e	*	*	*	FMAC	NP	*	*
10	a	*	*	*	FMAC	NP	*	*
Sheet

Excel Jeannie,

Thanks for your earlier replies. I did try your formula once again and it still didn't work. After examining the formula I found that the letters in your formula i.e. for column S had been transposed instead of it being FAMC, it was FMAC. hence the reason it wasn't working. I have changed this and now it appears to work perfectly. Thanks once again for all your help and patience.