Getting my head around SUMPRODUCT

[kterkuile]

Below is an example of a sheet in which I collect data on the quality of the work each employee delivers. From column H onwards I register if an employee did the CPI (Critical Process Indicator) correctly or not. One mistake and the formula in column D goes to 'no', but if all the indicators in a row are 'yes' or 'n.a.' it gives 'yes' as a result. So that's what i use as an indication of the quality of the handling of processes by the employees.

Now i want in columns E, F and G an indication on how they are doing on Dataquality etc. I want that as a percentage of what they did right of what they did right and wrong (rowtotal yes/(rowtotal yes + rowtotal no) but only on dataquality or only on customer satisfaction. So in cell E3 i'd expect 100% (H3:I3/H3:I3), but in cell E4 i'd expect 50%. In cell F3 I'd expect 100% again and in G3 I'd expect "".

I think using SUMPRODUCT is the answer, but I use it sporadically and can't seem to get my head aroud it. BTW: I use 2003. Would anyone care to help? Thanks in advance!

Take care,

Karel



<TABLE style="WIDTH: 828pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1105 border=0 x:str><COLGROUP><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 5741" width=157><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 804" width=22><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" span=7 width=111><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl42 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 21pt; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=28 height=17> </TD><TD class=xl43 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #969696; WIDTH: 118pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver" width=157>A</TD><TD class=xl43 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #969696; WIDTH: 53pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver" width=70>B</TD><TD class=xl43 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #969696; WIDTH: 17pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver" width=22>C</TD><TD class=xl43 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #969696; WIDTH: 38pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver" width=51>D</TD><TD class=xl43 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #969696; WIDTH: 83pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver" width=111>E</TD><TD class=xl43 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #969696; WIDTH: 83pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver" width=111>F</TD><TD class=xl43 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #969696; WIDTH: 83pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver" width=111>G</TD><TD class=xl43 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #969696; WIDTH: 83pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver" width=111>H</TD><TD class=xl43 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #969696; WIDTH: 83pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver" width=111>I</TD><TD class=xl43 style="BORDER-RIGHT: #969696 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #969696; WIDTH: 83pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver" width=111>J</TD><TD class=xl44 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #969696; WIDTH: 83pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver" width=111>K</TD></TR><TR style="HEIGHT: 57.75pt" height=77><TD class=xl45 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #969696; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 57.75pt; BACKGROUND-COLOR: silver" height=77 x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff">Employee</TD><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff">Acc Number</TD><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff">File Number</TD><TD class=xl41 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; WIDTH: 38pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff" width=51>File Correct</TD><TD class=xl25 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; WIDTH: 83pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff" width=111> </TD><TD class=xl25 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; WIDTH: 83pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff" width=111> </TD><TD class=xl25 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; WIDTH: 83pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff" width=111> </TD><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; WIDTH: 83pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff" width=111>CPI1</TD><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; WIDTH: 83pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff" width=111>CPI2</TD><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; WIDTH: 83pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff" width=111>CPI3</TD><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; WIDTH: 83pt; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff" width=111>CPI4</TD></TR><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD class=xl45 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #969696; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: silver" height=34 x:num>2</TD><TD class=xl27 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 118pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff" width=157> </TD><TD class=xl27 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 53pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff" width=70> </TD><TD class=xl27 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 17pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff" width=22> </TD><TD class=xl27 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 38pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff" width=51> </TD><TD class=xl39 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 83pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ffff99" width=111>Dataquality</TD><TD class=xl40 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 83pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffcc" width=111>Customer Satisfaction</TD><TD class=xl28 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 83pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff" width=111>Dataquality/Customer satisfaction</TD><TD class=xl39 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 83pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ffff99" width=111>Dataquality</TD><TD class=xl39 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 83pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ffff99" width=111>Dataquality</TD><TD class=xl40 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 83pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffcc" width=111>Customer Satisfaction</TD><TD class=xl28 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 83pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff" width=111>Dataquality/Customer satisfaction</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl45 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #969696; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17 x:num>3</TD><TD class=xl29 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: #ffcc00">Employee</TD><TD class=xl30 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccccff" x:num>123456789</TD><TD class=xl31 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff" x:num>1</TD><TD class=xl32 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff">yes</TD><TD class=xl33 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl33 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl33 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl34 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver">yes</TD><TD class=xl34 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver">yes</TD><TD class=xl34 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver">yes</TD><TD class=xl34 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver">n.a.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl45 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #969696; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17 x:num>4</TD><TD class=xl35 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: white 0.5pt solid; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ffcc00">Employee</TD><TD class=xl30 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccccff" x:num>123456790</TD><TD class=xl36 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff" x:num x:fmla="=+D4+1">2</TD><TD class=xl37 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff">no</TD><TD class=xl36 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl36 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl36 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl38 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: silver">yes</TD><TD class=xl38 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: silver">no</TD><TD class=xl38 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: silver">n.a.</TD><TD class=xl38 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: silver">yes</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl45 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #969696; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17 x:num>5</TD><TD class=xl29 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: #ffcc00">Employee</TD><TD class=xl30 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccccff" x:num>123456791</TD><TD class=xl31 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff" x:num x:fmla="=+D5+1">3</TD><TD class=xl32 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff">yes</TD><TD class=xl33 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl33 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl33 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl34 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver">yes</TD><TD class=xl34 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver">yes</TD><TD class=xl34 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver">n.a.</TD><TD class=xl34 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver">n.a.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl45 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #969696; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17 x:num>6</TD><TD class=xl35 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: white 0.5pt solid; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ffcc00">Employee</TD><TD class=xl30 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccccff" x:num>123456792</TD><TD class=xl36 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff" x:num x:fmla="=+D6+1">4</TD><TD class=xl37 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff">yes</TD><TD class=xl36 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl36 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl36 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl38 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: silver">yes</TD><TD class=xl38 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: silver">yes</TD><TD class=xl38 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: silver">n.a.</TD><TD class=xl38 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: silver">yes</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl45 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #969696; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17 x:num>7</TD><TD class=xl29 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: #ffcc00">Employee</TD><TD class=xl30 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccccff" x:num>123456793</TD><TD class=xl31 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff" x:num x:fmla="=+D7+1">5</TD><TD class=xl32 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff">yes</TD><TD class=xl33 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl33 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl33 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl34 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver">yes</TD><TD class=xl34 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver">yes</TD><TD class=xl34 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver">n.a.</TD><TD class=xl34 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver">n.a.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl45 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #969696; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17 x:num>8</TD><TD class=xl35 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: white 0.5pt solid; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ffcc00">Employee</TD><TD class=xl30 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccccff" x:num>123456794</TD><TD class=xl36 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff" x:num x:fmla="=+D8+1">6</TD><TD class=xl37 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff">yes</TD><TD class=xl36 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl36 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl36 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl38 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: silver">yes</TD><TD class=xl38 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: silver">yes</TD><TD class=xl38 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: silver">n.a.</TD><TD class=xl38 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: silver">n.a.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl45 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #969696; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #969696 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17 x:num>9</TD><TD class=xl29 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: white 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: #ffcc00">Employee</TD><TD class=xl30 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccccff" x:num>123456795</TD><TD class=xl31 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff" x:num x:fmla="=+D9+1">7</TD><TD class=xl32 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff">yes</TD><TD class=xl33 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl33 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl33 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl34 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver">yes</TD><TD class=xl34 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver">yes</TD><TD class=xl34 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver">n.a.</TD><TD class=xl34 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: silver">n.a.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl46 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #969696; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17 x:num>10</TD><TD class=xl35 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: white 0.5pt solid; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ffcc00">Employee</TD><TD class=xl30 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccccff" x:num>123456796</TD><TD class=xl36 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff" x:num x:fmla="=+D10+1">8</TD><TD class=xl37 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff">no</TD><TD class=xl36 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl36 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl36 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #ccffff"> </TD><TD class=xl38 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: silver">yes</TD><TD class=xl38 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: silver">yes</TD><TD class=xl38 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: silver">yes</TD><TD class=xl38 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: silver">no</TD></TR></TBODY></TABLE>

 

[njimack]

Try the following in E3, and copy down and across:
=SUMPRODUCT(--($H$2:$K$2=E$2),--($H3:$K3="yes"))/COUNTIF($H$2:$K$2,E$2)

Note this will return 0% if you have entered "n.a"

 

[kterkuile]

Hi Njimack,

Thanks, that did the job smoothly. I very much like the COUNTIF; nice touch! I've perked your formula up with a nice automatically adapting named range and showed it to my boss this morning. He's very satisfied with your work!

Thanks again for the help.

Take care,

Karel