Count unique with multiple columns and criteria

[anglais428]

Hi,

I am looking for a formula to analyze a data table to pull results into a new table (see below). The formula will need to count unique items in one column, with criteria in two other columns. In the example below, in the "expected results" table the value for X in 2012 is 3 because there are three unique occurrences of the Company name for the Location X and Year 2012 (these are A,B,C). This could be achieved with a normal COUNTIFS formula. The difficulty is for example for Location Z in 2012. Company D appears twice in Location Z, Year 2012 but I would like it only to be counted as 1 (i.e. unique counts only).

 

[DataBlake]

can you post your real data ranges and table names?

 

[anglais428]

A1:C9 for the first table.
G1:J5 for the second table.

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H	I	J
1		Location	Year				Location	2012	2013	2014
2		x	2013				x	3	1	1
3		x	2012				y	1	0	0
4		x	2012				z	2	0	0
5		x	2012
6		z	2012
7		y	2012
8		x	2014
9		z	2012
List

Cell Formulas
Range	Formula
H2:J4	H2	=COUNTIFS($B$2:$B$9,$G2,$C$2:$C$9,H$1)

 

[anglais428]

Hi Fluff,

Thanks for your reply. However, it is missing the key component (column A) as this is what I need to be unique counts. I have been trying variations of SUMIF(Frequency(IF but not getting very far.

 

[Fluff]

Missed that part, how about

Book1
	A	B	C	D	E	F	G	H	I	J
1		Location	Year				Location	2012	2013	2014
2	a	x	2013				x	3	1	1
3	a	x	2012				y	1	0	0
4	b	x	2012				z	1	0	0
5	c	x	2012
6	d	z	2012
7	e	y	2012
8	c	x	2014
9	d	z	2012
List

Cell Formulas
Range	Formula
H2:J4	H2	{=SUM(--(FREQUENCY(IF(($B$2:$B$9=$G2)*($C$2:$C$9=H$1),MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$A$9)-ROW($A$2)+1)>0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[anglais428]

Perfect. Exactly what I need. thanks

 

[Fluff]

You're welcome & thanks for the feedback

 

[hassanahmed]

Hi Fluff,
is there any way not to use Array Formula and still count unique values with criteria?

Thanks!

 

[Peter_SSs]

is there any way not to use Array Formula and still count unique values with criteria?

What version of Excel do you have?

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.