If Statment To Find Percent Based On Yes Or No

rockyw

Well-known Member
Joined
Dec 26, 2010
Messages
1,191
I have a list of 13 columns that could be yes or no. I need the percent that is marked as yes. Can I use an IF formula?? The formula will be in D6 looking at F14:F26, Thanks
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

rockyw

Well-known Member
Joined
Dec 26, 2010
Messages
1,191
Thanks that worked. Now can I use this formula to find the values in column E that go with the names in column B, is this close? Thanks
=SUM(($E$14:$E$26)*($B$14:$B$26=A6))
 

rockyw

Well-known Member
Joined
Dec 26, 2010
Messages
1,191
I have this formula that I need to change to row 26, when I do that I get an error. Why would this happen? Thnaks
=SUM(($F$14:$F$25="yes")*($B$14:$B$25=A6))
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256

ADVERTISEMENT

Thanks that worked. Now can I use this formula to find the values in column E that go with the names in column B, is this close? Thanks

Like this?
Excel 2010
ABE
126
13
14yes1
15yes1
16yes1
17no1
18no1
19no1
20no1
21no1
22no1
23no1
24yes1
25yes1
26yes1

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

Worksheet Formulas
CellFormula
A12=SUMIF($B$14:$B$26,A6,$E$14:$E$26)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>


Not sure about the second request.
Do you want to count all occurance of A6 in column B that match "yes" in column F?
 
Last edited:

rockyw

Well-known Member
Joined
Dec 26, 2010
Messages
1,191
That works great,now can I add one more thing. Can I base that off in column F is a Yes or No. I have this so far.
Code:
=SUM(($F$14:$F$26="yes")*($B$14:$B$26=A6,E14:E26))

Is this close? Thanks
 

rockyw

Well-known Member
Joined
Dec 26, 2010
Messages
1,191

ADVERTISEMENT

=SUM(($F$14:$F$25="yes")*($B$14:$B$25=A6))

Yes all I want to do is add one more row, to 26 but the formula stops working when I do that. I don't understand that?
 

rockyw

Well-known Member
Joined
Dec 26, 2010
Messages
1,191
Bump, can anyone help with this please? Column F has Yes or No, column B has names, A6 has one of the names in B. If column F has a yes for the name in A6 the formula adds the sum in column E for that name. So if there are 3 of the same name it adds all the sums behind he name in column E. I had this but will not work. Thanks
=SUM(($F$14:$F$26="yes")*($B$14:$B$26=A6,E14:E26))</PRE>
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style=";">Excel 03/07/10</td><td style=";">Excel 07/10</td><td style=";">Excel 03/07/10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="background-color: #FFFF00;;">robert</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style=";">Joe</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";">yes</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style=";">Robert</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";">yes</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style=";">Jerry</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";">yes</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style=";">Robert</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";">no</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style=";">Joe</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";">no</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style=";">mama</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";">no</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;"></td><td style=";">kasia</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";">no</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;"></td><td style=";">Joe</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";">no</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;"></td><td style=";">Gui</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";">no</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;"></td><td style=";">papa</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";">no</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;"></td><td style=";">mumu</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";">yes</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;"></td><td style=";">ffuf</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";">yes</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;"></td><td style=";">mama</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";">yes</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" 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: #BBB"><thead><tr style=" background-color: #DAE7F5;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: #DAE7F5;color: #161120">C6</th><td style="text-align:left">=SUMIFS(<font color="Blue">E14:E26,$F$14:$F$26,"yes",$B$14:$B$26,A6</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D6</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$F$14:$F$26="yes"</font>),--(<font color="Red">$B$14:$B$26=A6</font>),E14:E26</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" 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>Array 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: #BBB"><thead><tr style=" background-color: #DAE7F5;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: #DAE7F5;color: #161120">B6</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">$F$14:$F$26="yes",IF(<font color="Green">$B$14:$B$26=A6,E14:E26</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

rockyw

Well-known Member
Joined
Dec 26, 2010
Messages
1,191
Thank you for the help. I never worked with an Aray before. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,122,669
Messages
5,597,474
Members
414,145
Latest member
lonnie451

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top