Return number of unique records from multiple criteria

[stevemc2]

Hello

I have a list of records that also contains various fields - and I should like to try and determine the number of unique references in one column based on criteria entered in others. Hopefully I can explain the requirements and can interpret this table to explain the result I would expect - assuming the table below is in cells A1 to F26.

If the criteria was
AREA = 106; TYPE = E or TYPE = X; LOCATION = Stockport; DATE is between 01/01/2010 and 31/12/2010 and INCLUDE = Y

then I am looking for the unique number of references returned in column F.
I think the answer should be 2 - being one record of NDEIBKN and two records of NDAAUYB.

Thanks for any assistance

AREA	TYPE	LOCATION	DATE	INCLUDE	REFERENCE
106	E	Filey	30/04/2008		NCZHCWM
106	E	Kirkby	29/11/2007	Y	LCEGXBM
106	E	Filey	30/11/2005		NGEEXWX
106	E	Stockport	08/07/2010	Y	NDAAUYB
106	E	Stockport	01/03/2010		NJAZVVX
106	E	Stockport	01/03/2007		NXZGUWR
106	E	Kirkby	27/01/2010	Y	NDEIZTZ
106	E	Flint	01/09/2009	Y	YGEIFWH
106	X	Stockport	04/12/2010	Y	NDEIBKN
106	E	Stockport	20/05/2009		KUZIKHD
106	E	Kirkby	03/03/2004		PNZDLLR
106	W	Stockport	25/10/2010	Y	NAFZDXL
106	E	Kirkby	11/01/2007		NCFGULN
106	W	Stockport	25/10/2010		NAFZDXL
106	E	Newtown	24/01/2002		NGEAWDA
106	X	Stockport	30/11/2005		NDEENPC
106	E	Kirkby	15/09/2006		LGFGUTY
106	E	Filey	30/05/2007		BJZGOOY
106	E	Kirkby	30/06/2008		BLZHKWH
106	E	Kirkby	29/11/2007	Y	LCEGXBM
106	E	Flint	01/09/2009	Y	YGEIFWH
106	X	Stockport	08/07/2010	Y	NDAAUYB
104	E	Stockport	01/03/2010	Y	NJAZVVX
106	E	Stockport	01/03/2007	Y	NXZGUWR
105	E	Kirkby	27/01/2010	Y	NDEIZTZ

<tbody>
</tbody>

 

[Ishan]

Hi stevemc2,


I got this from Pivot table. Try it and Enjoy. Also donot forget to convert date into MM/DD/YYYY

DATE	REFERENCE	AREA	TYPE	LOCATION	INCLUDE
7/8/2010	NDAAUYB	106	E	Stockport	Y
			X	Stockport	Y
3/1/2010	NJAZVVX	104	E	Stockport	Y
		106	E	Stockport	(blank)
1/27/2010	NDEIZTZ	105	E	Kirkby	Y
		106	E	Kirkby	Y
12/4/2010	NDEIBKN	106	X	Stockport	Y
10/25/2010	NAFZDXL	106	W	Stockport	Y
					(blank)
Grand Total

<col span="2"><col><col span="2"><col><col span="6"><tbody>
</tbody>

Regards,


Ishan

 

[stevemc2]

Thanks Ishan

I did mean to say (but forgot before posting) that I was hoping for a formula to give me the results. Would you (or anyone else) know how to do it this way?

 

[stevemc2]

Something else I meant to add but also forgot. I am using Excel 2010.

 

[raj2206]

Hey Thanks for writing!

You can use the following Formula assuming that your data is A1:F26, change the refrence as per the requirement:

=SUM(IFERROR(1/COUNTIF(F2:F26,IF((A2:A26=J1)*((B2:B26=K2)+(B2:B26=J2))*(C2:C26=J3)*(D2:D26>=J4)*(D2:D26<=K4)*(E2:E26=J5)=0,0,F2:F26)),0))

Put the velues in the following cells so that you can make the criteria dynamic:

J1 = 106
J2 = E
J3 = Stockport
J4 = 1/1/2010
J5 = Y
K2 = X
K4 = 31/12/2010

Also make sure you press Control+Shift+Enter while entering the formula since its an array operation.

Hope this helps.

Thanks/Raj

 

[stevemc2]

Raj

Thanks very much that looks to have worked - and as I want to use it with dynamic data anyway even better. I will now try and further the formula to meet the requirements of the larger table that I have.