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!

 

[MARZIOTULLIO]

​[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]AL001[/TD]
[TD]Birmingham[/TD]
[TD][/TD]
[TD][/TD]
[TD]criteria[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]AL001[/TD]
[TD]CV[/TD]
[TD][/TD]
[TD][/TD]
[TD]Birmingham+ PASSED[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]AL001[/TD]
[TD]PHY[/TD]
[TD]PASSED[/TD]
[TD][/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]AL001[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]AL001[/TD]
[TD]Vendor[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]AL002[/TD]
[TD]Birmingham[/TD]
[TD]PASSED[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]AL002[/TD]
[TD]PHY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]AL002[/TD]
[TD]Vendor[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]AL003[/TD]
[TD]Birmingham[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]AL003[/TD]
[TD]DA[/TD]
[TD]PASSED[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

 

[kalmar27615]

​[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]AL001[/TD]
[TD]Birmingham[/TD]
[TD][/TD]
[TD][/TD]
[TD]criteria[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]AL001[/TD]
[TD]CV[/TD]
[TD][/TD]
[TD][/TD]
[TD]Birmingham+ PASSED[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]AL001[/TD]
[TD]PHY[/TD]
[TD]PASSED[/TD]
[TD][/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]AL001[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]AL001[/TD]
[TD]Vendor[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]AL002[/TD]
[TD]Birmingham[/TD]
[TD]PASSED[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]AL002[/TD]
[TD]PHY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]AL002[/TD]
[TD]Vendor[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]AL003[/TD]
[TD]Birmingham[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]AL003[/TD]
[TD]DA[/TD]
[TD]PASSED[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]AL001[/TD]
[TD="bgcolor: #FAFAFA"]Birmingham[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]AL001[/TD]
[TD="bgcolor: #FAFAFA"]CV[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]AL001[/TD]
[TD="bgcolor: #FAFAFA"]PHY[/TD]
[TD="bgcolor: #FAFAFA"]PASSED[/TD]
[TD="bgcolor: #FAFAFA"]Birmingham[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]AL001[/TD]
[TD="bgcolor: #FAFAFA"]No[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]AL001[/TD]
[TD="bgcolor: #FAFAFA"]Vendor[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]AL002[/TD]
[TD="bgcolor: #FAFAFA"]Birmingham[/TD]
[TD="bgcolor: #FAFAFA"]PASSED[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]AL002[/TD]
[TD="bgcolor: #FAFAFA"]PHY[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]AL002[/TD]
[TD="bgcolor: #FAFAFA"]Vendor[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]AL003[/TD]
[TD="bgcolor: #FAFAFA"]Birmingham[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]AL003[/TD]
[TD="bgcolor: #FAFAFA"]DA[/TD]
[TD="bgcolor: #FAFAFA"]PASSED[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]{=SUM(--(COUNTIFS(A1:A10,IF(B1:B10=D3,A1:A10,"^^^"),C1:C10,"PASSED")>0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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:

[TABLE="width: 453"]
<colgroup><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Civil[/TD]
[TD]AL001[/TD]
[TD]Birmingham[/TD]
[TD] [/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Civil[/TD]
[TD]AL001[/TD]
[TD]CV[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Civil[/TD]
[TD]AL001[/TD]
[TD]PHY[/TD]
[TD]PASSED[/TD]
[TD]Birmingham[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Civil[/TD]
[TD]AL001[/TD]
[TD]No[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Civil[/TD]
[TD]AL001[/TD]
[TD]Vendor[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Civil[/TD]
[TD]AL002[/TD]
[TD]Birmingham[/TD]
[TD]PASSED[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Civil[/TD]
[TD]AL002[/TD]
[TD]PHY[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Civil[/TD]
[TD]AL002[/TD]
[TD]Vendor[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Civil[/TD]
[TD]AL003[/TD]
[TD]Birmingham[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Civil[/TD]
[TD]AL003[/TD]
[TD]DA[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Elec[/TD]
[TD]AL003[/TD]
[TD]Birmingham[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Elec[/TD]
[TD]AL003[/TD]
[TD]DA[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Elec[/TD]
[TD]AL003[/TD]
[TD]DA[/TD]
[TD]PASSED[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Civil[/TD]
[TD="bgcolor: #FAFAFA"]AL001[/TD]
[TD="bgcolor: #FAFAFA"]Birmingham[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Civil[/TD]
[TD="bgcolor: #FAFAFA"]AL001[/TD]
[TD="bgcolor: #FAFAFA"]CV[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Civil[/TD]
[TD="bgcolor: #FAFAFA"]AL001[/TD]
[TD="bgcolor: #FAFAFA"]PHY[/TD]
[TD="bgcolor: #FAFAFA"]PASSED[/TD]
[TD="bgcolor: #FAFAFA"]Birmingham[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Civil[/TD]
[TD="bgcolor: #FAFAFA"]AL001[/TD]
[TD="bgcolor: #FAFAFA"]No[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Civil[/TD]
[TD="bgcolor: #FAFAFA"]AL001[/TD]
[TD="bgcolor: #FAFAFA"]Vendor[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]Civil[/TD]
[TD="bgcolor: #FAFAFA"]AL002[/TD]
[TD="bgcolor: #FAFAFA"]Birmingham[/TD]
[TD="bgcolor: #FAFAFA"]PASSED[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]Civil[/TD]
[TD="bgcolor: #FAFAFA"]AL002[/TD]
[TD="bgcolor: #FAFAFA"]PHY[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]Civil[/TD]
[TD="bgcolor: #FAFAFA"]AL002[/TD]
[TD="bgcolor: #FAFAFA"]Vendor[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]Civil[/TD]
[TD="bgcolor: #FAFAFA"]AL003[/TD]
[TD="bgcolor: #FAFAFA"]Birmingham[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]Civil[/TD]
[TD="bgcolor: #FAFAFA"]AL003[/TD]
[TD="bgcolor: #FAFAFA"]DA[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]Elec[/TD]
[TD="bgcolor: #FAFAFA"]AL003[/TD]
[TD="bgcolor: #FAFAFA"]Birmingham[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA"]Elec[/TD]
[TD="bgcolor: #FAFAFA"]AL003[/TD]
[TD="bgcolor: #FAFAFA"]DA[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA"]Elec[/TD]
[TD="bgcolor: #FAFAFA"]AL003[/TD]
[TD="bgcolor: #FAFAFA"]DA[/TD]
[TD="bgcolor: #FAFAFA"]PASSED[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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.


[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]{=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))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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)