Return Fail/Pass

nycard64

Board Regular
Joined
Dec 23, 2009
Messages
113
I have a sheet where I need to match a pass or fail to line up with a specific tool ID.

Column D ( has tool list in it ), Column O ( has results ) I need to match up passes and fails from column O to the specific tools listed in Column D

Chart Summary

<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 69px"> <col style="WIDTH: 73px"> <col style="WIDTH: 63px"> <col style="WIDTH: 46px"> <col style="WIDTH: 74px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"></colgroup> <tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td> </td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td> <td>L</td> <td>M</td> <td>N</td> <td>O</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Group</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Mean</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Yield</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">N</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">StdDev</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Min</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">PTile05</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">PTile25</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Median</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">PTile75</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">PTile95</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Max</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JI01</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.545565963</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.545565963</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.545565963</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.545565963</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.545565963</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.545565963</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.545565963</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.545565963</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JI01</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.02989709</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.02989709</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.02989709</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.02989709</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.02989709</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.02989709</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.02989709</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.02989709</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JI01</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.053500064</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.053500064</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.053500064</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.053500064</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.053500064</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.053500064</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.053500064</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.053500064</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JI01</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.050352998</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.050352998</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.050352998</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.050352998</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.050352998</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.050352998</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.050352998</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.050352998</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
I did not have all the columns copied. See column P for pass of fail formula. But would like the count fails and passes by tool in Sheet 2. Sorry if the first one was confusing.

Chart Summary

<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 69px"> <col style="WIDTH: 73px"> <col style="WIDTH: 63px"> <col style="WIDTH: 46px"> <col style="WIDTH: 74px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 73px"> <col style="WIDTH: 55px"></colgroup> <tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td> </td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td> <td>L</td> <td>M</td> <td>N</td> <td>O</td> <td>P</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Group</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Mean</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Yield</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">N</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">StdDev</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Min</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">PTile05</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">PTile25</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Median</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">PTile75</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">PTile95</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Max</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Fail</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JI01</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.545565963</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.545565963</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.545565963</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.545565963</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.545565963</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.545565963</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.545565963</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.545565963</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Fail</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JI01</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.02989709</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.02989709</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.02989709</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.02989709</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.02989709</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.02989709</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.02989709</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.02989709</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Pass</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JI01</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.053500064</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.053500064</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.053500064</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.053500064</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.053500064</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.053500064</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.053500064</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.053500064</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Pass</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JI01</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.050352998</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.050352998</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.050352998</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.050352998</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.050352998</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.050352998</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.050352998</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.050352998</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Pass</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JI01</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.022029459</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.022029459</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.022029459</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.022029459</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.022029459</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.022029459</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.022029459</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.022029459</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Pass</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JI01</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.045632459</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.045632459</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.045632459</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.045632459</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.045632459</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.045632459</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.045632459</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.045632459</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Pass</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JI01</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.0157353</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.0157353</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.0157353</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.0157353</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.0157353</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.0157353</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.0157353</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">0.0157353</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Pass</td></tr></tbody></table>
<table style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>P2</td> <td>=IF(O2>0.15, "Fail", "Pass")</td></tr> <tr> <td>P3</td> <td>=IF(O3>0.15, "Fail", "Pass")</td></tr> <tr> <td>P4</td> <td>=IF(O4>0.15, "Fail", "Pass")</td></tr> <tr> <td>P5</td> <td>=IF(O5>0.15, "Fail", "Pass")</td></tr> <tr> <td>P6</td> <td>=IF(O6>0.15, "Fail", "Pass")</td></tr> <tr> <td>P7</td> <td>=IF(O7>0.15, "Fail", "Pass")</td></tr> <tr> <td>P8</td> <td>=IF(O8>0.15, "Fail", "Pass")</td></tr></tbody></table></td></tr></tbody></table>
Sheet2

<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 64px"> <col style="WIDTH: 75px"> <col style="WIDTH: 64px"></colgroup> <tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td> </td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="HEIGHT: 17px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</td> <td>Tool</td> <td style="TEXT-ALIGN: center">Pass</td> <td style="TEXT-ALIGN: center">Fail</td></tr> <tr style="HEIGHT: 17px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td>JI01</td> <td> </td> <td> </td></tr> <tr style="HEIGHT: 17px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td>JI02</td> <td> </td> <td> </td></tr> <tr style="HEIGHT: 17px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</td> <td>JID1</td> <td> </td> <td> </td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4
Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
<b>Excel 2007</b><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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Pass</td><td style=";">Fail</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">JI01</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">JI02</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</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: #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">B2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">Sheet1!$D$2:$D$8,Sheet2!$A2,Sheet1!$P$2:$P$8,Sheet2!B$1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=COUNTIFS(<font color="Blue">Sheet1!$D$2:$D$8,Sheet2!$A2,Sheet1!$P$2:$P$8,Sheet2!C$1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=COUNTIFS(<font color="Blue">Sheet1!$D$2:$D$8,Sheet2!$A3,Sheet1!$P$2:$P$8,Sheet2!B$1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=COUNTIFS(<font color="Blue">Sheet1!$D$2:$D$8,Sheet2!$A3,Sheet1!$P$2:$P$8,Sheet2!C$1</font>)</td></tr></tbody></table></td></tr></table><br />

<b>Excel 2007</b><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 /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</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><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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">JI01</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><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=";">Pass</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">JI02</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><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=";">Fail</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">JI01</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><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=";">Fail</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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />
 
Upvote 0
You can use a formula like this:

=SUMPRODUCT(--('Chart Summary'!$D$2:$D$8=$A2),--('Chart Summary'!$P$2:$P$8=B$1))

adjust the Chart Summary ranges to suit, but make sure that they both have the same number of rows and avoid using entire columns.

If you have Excel 2007 or above try the untested:

=COUNTIFS('Chart Summary'!$D$2:$D$8,$A2,'Chart Summary'!$P$2:$P$8,B$1)
 
Upvote 0

Forum statistics

Threads
1,223,152
Messages
6,170,389
Members
452,323
Latest member
GoJones

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
Back
Top