Count unique values with a couple of criteria's

[Cantrecallmyusername]

Hi there,
I have some data which I want to count unique values based on the values in a specific column and it meets 2 other criteria.
The high level is that I want to count how many unique terms are in my raw data tab if they are marked approved in a column and belong to a dept.

1. Term is in Column A on Raw Data Tab
2. Status is column B on Raw Data Tab
3. Dept is in column S on Raw Data Tab

Status can be approved or Under Review and I have these in cellsT1 and T2 on my Stats Tab
The dept names are listed in cells B4:BB10 on the stats tab

I have tried the following formula but needs the second criteria passed into also (the dept names) using Ctrl + Shift

=SUM(IF(T1='Raw Data'!B:B, 1/(COUNTIFS('Raw Data'!B:B, T1, 'Raw Data'!A:A,'Raw Data'!A:A)), 0))

Any help would be greatly appreciated.

 

[JamesCanale]

365 makes quick work of it:

MrExcelPlayground12.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Term	Status																	Dept
2	A	Approved																	X	3
3	B	Under Review																	X
4	C	Approved																	Y
5	B	Approved																	Y
6	C	Approved																	Z
7	E	Under Review																	Z
8	A	Under Review																	X
9	B	Approved																	X
10	C	Approved																	X
11	D	Under Review																	X
12	E	Approved																	Y
13	A	Approved																	X
Sheet28

Cell Formulas
Range	Formula
T2	T2	=ROWS(UNIQUE(FILTER(A2:A13,(B2:B13="Approved")*(S2:S13="X"))))

 

[Cantrecallmyusername]

365 makes quick work of it:

MrExcelPlayground12.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Term	Status																	Dept
2	A	Approved																	X	3
3	B	Under Review																	X
4	C	Approved																	Y
5	B	Approved																	Y
6	C	Approved																	Z
7	E	Under Review																	Z
8	A	Under Review																	X
9	B	Approved																	X
10	C	Approved																	X
11	D	Under Review																	X
12	E	Approved																	Y
13	A	Approved																	X
Sheet28

Cell Formulas
Range	Formula
T2	T2	=ROWS(UNIQUE(FILTER(A2:A13,(B2:B13="Approved")*(S2:S13="X"))))

Thanks for this however I dont have 365 on my PC for this to work - I am getting the "#CALC!" error with this , any similar function/formula that would work for me?

 

[AhoyNC]

Maybe something like this.

Book2
	A	B																R	S	T	U
1	Term	Status																	Dept	Approved	3
2	A	Approved																	X	Under Review	3
3	B	Under Review																	X
4	C	Approved																	Y
5	B	Approved																	Y
6	C	Approved																	Z
7	E	Under Review																	Z
8	A	Under Review																	X
9	B	Approved																	X
10	C	Approved																	X
11	D	Under Review																	X
12	E	Approved																	Y
13	A	Approved																	X
Sheet1

Cell Formulas
Range	Formula
U1:U2	U1	=SUM(IF(FREQUENCY(IF($B$2:$B$13<>"",IF($B$2:$B$13=T1,IF($S$2:$S$13="x",MATCH($A$2:$A$13&"/"&$B$2:$B$13,$A$2:$A$13&"/"&$B$2:$B$13,0)))),ROW($A$2:$A$13)-ROW($A$2)+1),1))

 

[Cantrecallmyusername]

Maybe something like this.

Book2
	A	B																R	S	T	U
1	Term	Status																	Dept	Approved	3
2	A	Approved																	X	Under Review	3
3	B	Under Review																	X
4	C	Approved																	Y
5	B	Approved																	Y
6	C	Approved																	Z
7	E	Under Review																	Z
8	A	Under Review																	X
9	B	Approved																	X
10	C	Approved																	X
11	D	Under Review																	X
12	E	Approved																	Y
13	A	Approved																	X
Sheet1

Cell Formulas
Range	Formula
U1:U2	U1	=SUM(IF(FREQUENCY(IF($B$2:$B$13<>"",IF($B$2:$B$13=T1,IF($S$2:$S$13="x",MATCH($A$2:$A$13&"/"&$B$2:$B$13,$A$2:$A$13&"/"&$B$2:$B$13,0)))),ROW($A$2:$A$13)-ROW($A$2)+1),1))

Hi,
Thanks for this - I have tried the formula provided in my spreadsheet where I have the source data on a tab called Raw Data but it returns 0
X in your formula is cell B4 in my sheet. Any ideas on why this might not be working?

=SUM(IF(FREQUENCY(IF('Raw Data'!B:B<>"",IF('Raw Data'!B:B=$T$1,IF('Raw Data'!S:S=B4,MATCH('Raw Data'!A:A&"/"&'Raw Data'!B:B,'Raw Data'!A:A&"/"&'Raw Data'!B:B,0)))),ROW('Raw Data'!A:A)-ROW('Raw Data'!A2)+1),1))

 

[Cantrecallmyusername]

Hi,
Thanks for this - I have tried the formula provided in my spreadsheet where I have the source data on a tab called Raw Data but it returns 0
X in your formula is cell B4 in my sheet. Any ideas on why this might not be working?

=SUM(IF(FREQUENCY(IF('Raw Data'!B:B<>"",IF('Raw Data'!B:B=$T$1,IF('Raw Data'!S:S=B4,MATCH('Raw Data'!A:A&"/"&'Raw Data'!B:B,'Raw Data'!A:A&"/"&'Raw Data'!B:B,0)))),ROW('Raw Data'!A:A)-ROW('Raw Data'!A2)+1),1))

Ignore I have figured it out, thank you.