Percentage of cells that meet a given criteria if cells in another column meet a given criteria

[dap36]

This is a problem I have been trying to resolve for quite a while unsuccessfully.

I need to know what percentage of the non-black cells in column C are either a 1 or a 2, but only if the value in column be is a 1 or a 2.
So in my example, there are 9 cells in column B that are a 1 or a 2. Only 8 of the corresponding cells in column C have a non-zero value. Since 4 of those 8 values are a 1 or a 2, then the final output would be 50% (4 out of 8).


B C
1 1
2 3
1 1
3 3
2
1 5
5 3
2 5
2 3
2 1
1 2

Thank you for any help you can provide.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG01Sep17
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, B, C
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Dn = 1 Or Dn = 2 And Dn.Offset(, 1) <> "" [COLOR="Navy"]Then[/COLOR]
    B = B + 1
    [COLOR="Navy"]If[/COLOR] Dn.Offset(, 1) = 1 Or Dn.Offset(, 1) = 2 [COLOR="Navy"]Then[/COLOR] C = C + 1
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
MsgBox Format(C / B, "0%")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">50%</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">1</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">2</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">(<font color="Green">B1:B11=1</font>)+(<font color="Green">B1:B11=2</font>)</font>)*(<font color="Red">(<font color="Green">C1:C11=1</font>)+(<font color="Green">C1:C11=2</font>)</font>)</font>)/SUMPRODUCT(<font color="Blue">(<font color="Red">(<font color="Green">B1:B11=1</font>)+(<font color="Green">B1:B11=2</font>)</font>)*(<font color="Red">C1:C11<>""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />