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")

AL001	Birmingham
AL001	CV
AL001	PHY	PASSED
AL001	No
AL001	Vendor
AL002	Birmingham	PASSED
AL002	PHY
AL002	Vendor
AL003	Birmingham
AL003	DA	PASSED

<tbody>
</tbody>

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!

 

[MARZIOTULLIO]

​

	A​	B​	C​	D​	E​
1​	AL001	Birmingham			criteria
2​	AL001	CV			Birmingham+ PASSED
3​	AL001	PHY	PASSED		6​
4​	AL001	No
5​	AL001	Vendor
6​	AL002	Birmingham	PASSED
7​	AL002	PHY
8​	AL002	Vendor
9​	AL003	Birmingham
10​	AL003	DA	PASSED

<tbody>
</tbody>

E3=SUMPRODUCT((B1:B10=B1)+(C1:C10=C3))

 

[kalmar27615]

​

	A​	B​	C​	D​	E​
1​	AL001	Birmingham			criteria
2​	AL001	CV			Birmingham+ PASSED
3​	AL001	PHY	PASSED		6​
4​	AL001	No
5​	AL001	Vendor
6​	AL002	Birmingham	PASSED
7​	AL002	PHY
8​	AL002	Vendor
9​	AL003	Birmingham
10​	AL003	DA	PASSED

<tbody>
</tbody>

E3=SUMPRODUCT((B1:B10=B1)+(C1:C10=C3))

Thank you, but the result I'm looking for is 3 (for 3 "PASSED" sites (AL001, AL002 and AL003) in Birmingham

 

[Eric W]

Based on your table, it appears that each "occurrence" has the same number in column A. So if lines 1:5 have Birmingham in column B somewhere, and 1:5 have PASSED in column C somewhere, you want to count it. Repeat for each group in column A.

If so, try:

	A	B	C	D
1	AL001	Birmingham		3
2	AL001	CV
3	AL001	PHY	PASSED	Birmingham
4	AL001	No
5	AL001	Vendor
6	AL002	Birmingham	PASSED
7	AL002	PHY
8	AL002	Vendor
9	AL003	Birmingham
10	AL003	DA	PASSED

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Array Formulas Cell Formula D1 {=SUM(--(COUNTIFS(A1:A10,IF(B1:B10=D3,A1:A10,"^^^"),C1:C10,"PASSED")>0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

D1 is an array formula, confirm with Control+Shift+Enter. The city name is in D3. Let me know if this is workable.

 

[kalmar27615]

Thank you Eric, this worked to an extent, but the way I have the table setup is there is another column with more variables and the formula returns 4 instead of 3 in this case:

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

<colgroup><col span="3"><col><col><col></colgroup><tbody>
</tbody>

 

[CyrusTheVirus]

What do you want the answer to be in new table, 3 or 4? Would you be able to clarify again what you are wanting?

 

[kalmar27615]

What do you want the answer to be in new table, 3 or 4? Would you be able to clarify again what you are wanting?

The answer I am looking for is 3. as in post #5: I have 3 sites (column B), some "Civil", some "Elec" and some are both. I need to count how many "PASSED" there are in column D for sites that have "Birmingham" for every group of sites

 

[Eric W]

The issue is that for group AL003, Birmingham occurs twice. It appears you only want to count once for each ALxxx group.

Try this:

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula E1 {=SUM(--(COUNTIFS(B1:B13,IF((C1:C13=E3)*IFERROR(MATCH(B1:B13&"|"&C1:C13,B1:B13&"|"&C1:C13,0)=ROW(C1:C13)-ROW(C1)+1,0),B1:B13,"^^^"),D1:D13,"PASSED")>0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This should work for you. It's a bit more complicated though. I might step back and try a different approach. But see how this goes for now.

 

[Eric W]

Here is a different approach. I haven't thoroughly tested this yet, but it looks good so far.

Book1
	A	B	C	D	E	F
1
2	Civil	AL001	Birmingham		3	3
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
12	Elec	AL003	Birmingham
13	Elec	AL003	DA
14	Elec	AL003	DA	PASSED
Sheet1

Cell Formulas
Range	Formula
E2	{=SUM(--(COUNTIFS(B2:B14,IF((C2:C14=E4)*IFERROR(MATCH(B2:B14&"|"&C2:C14,B2:B14&"|"&C2:C14,0)=ROW(C2:C14)-ROW(C2)+1,0),B2:B14,"^^^"),D2:D14,"PASSED")>0))}
F2	{=SUM(IF(FREQUENCY(IF(C2:C14=E4,ROW(C2:C14)),IF(B1:B15<>B2:B16,ROW(B1:B15)))*FREQUENCY(IF(D2:D14="PASSED",ROW(C2:C14)),IF(B1:B15<>B2:B16,ROW(B1:B15))),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

I don't know which is better, but FREQUENCY often works faster. The FREQUENCY^2 function requires slightly different ranges, adding a row before. Interesting problem, I'll look at it some more.

 

[kalmar27615]

The issue is that for group AL003, Birmingham occurs twice. It appears you only want to count once for each ALxxx group.

Array Formulas Cell Formula E1 {=SUM(--(COUNTIFS(B1:B13,IF((C1:C13=E3)*IFERROR(MATCH(B1:B13&"|"&C1:C13,B1:B13&"|"&C1:C13,0)=ROW(C1:C13)-ROW(C1)+1,0),B1:B13,"^^^"),D1:D13,"PASSED")>0))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This should work for you. It's a bit more complicated though. I might step back and try a different approach. But see how this goes for now.

That is correct, "Birmingham" could occur twice, but each group could also have "Civil" and "Elec" within the same group which will have their own "PASSED" that needs to be counted. Sorry, my previous post did not include the "PASSED" for AL003 "Civil" group. There also could be "FAILED" within the same group as well as "PASSED" ("FAILED" for AL003 Civil and "PASSED" for AL003 Elec and visa-versa)