COUNTIFS alternative

[kalmar27615]

Hello everyone, I have the following issue I'm hoping you can help me with:

I am trying to count the number of occurrences with 2 criteria: =COUNTIFS(B:B,"Birmingham",C:C,"PASSED")

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]AL001[/TD]
[TD]Birmingham[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AL001[/TD]
[TD]CV[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AL001[/TD]
[TD]PHY[/TD]
[TD]PASSED[/TD]
[/TR]
[TR]
[TD]AL001[/TD]
[TD]No[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AL001[/TD]
[TD]Vendor[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AL002[/TD]
[TD]Birmingham[/TD]
[TD]PASSED[/TD]
[/TR]
[TR]
[TD]AL002[/TD]
[TD]PHY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AL002[/TD]
[TD]Vendor[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AL003[/TD]
[TD]Birmingham[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AL003[/TD]
[TD]DA[/TD]
[TD]PASSED[/TD]
[/TR]
</tbody>[/TABLE]

The location of the "PASSED" entry is dynamic and is not always directly across from "Birmingham" which is why I am not getting the correct results. Is there a solution to this? Thank you all in advance!

 

[Eric W]

Please try to be specific with your requirements. It will make this move much more smoothly. Try this version:

Excel 2012
	A	B	C	D	E	F
1
2	Civil	AL001	Birmingham			4
3	Civil	AL001	CV
4	Civil	AL001	PHY	PASSED	Birmingham
5	Civil	AL001	No
6	Civil	AL001	Vendor
7	Civil	AL002	Birmingham	PASSED
8	Civil	AL002	PHY
9	Civil	AL002	Vendor
10	Civil	AL003	Birmingham
11	Civil	AL003	DA	PASSED
12	Elec	AL003	Birmingham
13	Elec	AL003	DA
14	Elec	AL003	DA	PASSED
Sheet1

Cell Formulas
Range	Formula
F2	{=SUM(IF(FREQUENCY(IF(C2:C14=E4,ROW(C2:C14)),IF((A1:A15<>A2:A16)+(B1:B15<>B2:B16),ROW(B1:B15)))*FREQUENCY(IF(D2:D14="PASSED",ROW(C2:C14)),IF((A1:A15<>A2:A16)+(B1:B15<>B2:B16),ROW(B1:B15))),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

This version will separate the list into ranges, each range demarked by every time column A or B changes. It will then add 1 every time there is at least 1 Birmingham in that range in column C, and at least 1 PASSED in that range in column D.

 

[kalmar27615]

Thank you Eric, that worked perfectly.

 

[Eric W]

Glad to help!