Count Values based on multiple critiera

[rac241985]

Hi All,

I want to count the total number of Pass and Fail based on Column 1 (Originating) site. For. Eg., If I select US, I should get 3 pass and 1 Fails, and if I select UK I should get 1 pass and 1 fails. Can someone please help?

[TABLE="width: 257"]
<tbody>[TR]
[TD]Originating Site[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Desired Output

Selected Site (Based on Dropdown) - US
Pass: 3
Fail: 1

Any help will be appreciated.

Thanks,
RAC

 

[jtakw]

Hi,

Use COUNTIFS:

Book1
	G	H	I	J	K	L
1	Originating Site	Description		Your Drop Down	Pass	Fail
2	US	Pass		US	3	1
3	US	Pass
4	UK	Pass
5	US	Fail
6	US	Pass
7	UK	Fail
Sheet527

Cell Formulas
Range	Formula
K2	=COUNTIFS($G2:$G7,$J2,$H2:$H7,K1)

K2 Formula copied over to L2

 

[jtakw]

And if your setup is Vertical:

Book1
	G	H	I	J	K
1	Originating Site	Description		Your Drop Down
2	US	Pass		US
3	US	Pass		Pass	3
4	UK	Pass		Fail	1
5	US	Fail
6	US	Pass
7	UK	Fail
Sheet527

Cell Formulas
Range	Formula
K3	=COUNTIFS(G$2:G$7,J$2,H$2:H$7,J3)

K3 formula copied down to K4