calculating row percent based on a due date and today

[lefty38]

I have a large table where the today's date and the due date needs to be part of the calculation
if count the row of columns where today's date is >= than the due date that = the denominator
(this example columns E,F,G are counted because row 7 less than today)


Then calculate the same columns (E,F,G) count the dates in the employee row as "completed" as the numerator


numerator / denominator = % (in cell d10 - D16 )

if the item is NR (not required) then do not use that as part of the
numerator or denominator

Column K has the expected results
I hope i am making sense
your help is greatly appreciated

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;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><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1Q</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1Q</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1Q</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2Q</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2Q</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2Q</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">data</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">data</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">data</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">data</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">data</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">data</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Required</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Completed</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">66.7%</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">80.0%</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">66.7%</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">75.0%</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">66.7%</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">66.7%</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #0000FF;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #0000FF;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #0000FF;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #0000FF;background-color: #C2D69A;;">Due dt</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #0000FF;background-color: #C2D69A;;">01/31/11</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #0000FF;background-color: #C2D69A;;">02/28/11</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #0000FF;background-color: #C2D69A;;">03/31/11</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #0000FF;background-color: #C2D69A;;">04/30/11</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #0000FF;background-color: #C2D69A;;">05/31/11</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #0000FF;background-color: #C2D69A;;">06/30/11</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">data</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">data</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">data</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">data</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">data</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">data</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Manager</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Employee</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Emp ID</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">% Comp</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">item 1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">item 2</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">item 3</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">item 4</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">item 5</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">item 6</td><td style="border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D7E4BC;;">Expected outcomes of D10 - D16</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Slate</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Fred</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">63155</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">=??</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">1/21/011</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">2/1/11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">3/31/11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3/15/11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3/16/11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3/17/11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D7E4BC;;">3/3 complete = 100%</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Slate</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Barnie</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">122861</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">=??</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">1/21/012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">4/1/11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3/16/11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3/18/11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D7E4BC;;">2/3 complete = 66.6%</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Spacley</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">George</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">82718</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">=??</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">NH</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">NH</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">NH</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">NH</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">NH</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">NH</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D7E4BC;;">NH = 0    0/3 items</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Spacley</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Jane</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">137293</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">=??</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">NR</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">NR</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">NR</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">NR</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">NR</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D7E4BC;;">NR = Not Counted  = NR </td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Spacley</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Elroy</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">72894</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">=??</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">NH</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">2/1/11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">NH</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">NH</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">NH</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">NH</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D7E4BC;;">1/3 complete = 33.3%</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Spacley</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Judy</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">72895</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">=??</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D7E4BC;;">0/3 complete = 0%</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Spacley</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Astro</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">72896</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">=??</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">NR</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">2/3/11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">NR</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">NH</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">NH</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">NH</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #D7E4BC;;">1/1 complete = 100%</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 />

 

[Jerry Sullivan]

Hello lefty38,

You could try:

=IFERROR(COUNTIFS(E$6:J$6,"<="&TODAY(),E9:J9,">0")/COUNTIFS(E$6:J$6,"<="&TODAY(),E9:J9,"<>NR"),"NR")

 

[lefty38]

JS411 thank you - but that did not work
I should of mentioned I am using 2007 for the demonstration - but the formula will be put back on version 2003

i had to edit the E9:J9 to E10:J10 but the results still brought back "NR" value for all row examples

to make it more clear of the expected formula results i added column C
Column K is just the reference of what should be counted in the formula
Example row 5
2 of the 3 items have been completed so the result should be 66.6%

again thank you for any help

=IFERROR(COUNTIFS(E$6:J$6,"<="&TODAY(),E10:J10,">0")/COUNTIFS(E$6:J$6,"<="&TODAY(),E9:J9,"<>NR"),"NR")

Excel 2007<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(224, 224, 240);" width="25px"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style="background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>A</th><th>B</th><th>C</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></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="font-weight: bold; text-align: center; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(255, 255, 255);">
</td><td style="font-weight: bold; text-align: center; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(255, 255, 255);">
</td><td style="font-weight: bold; text-align: center; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(255, 255, 255);">
</td><td style="font-weight: bold; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(194, 214, 154);">Due dt</td><td style="font-weight: bold; text-align: center; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(194, 214, 154);">01/31/11</td><td style="font-weight: bold; text-align: center; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(194, 214, 154);">02/28/11</td><td style="font-weight: bold; text-align: center; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(194, 214, 154);">03/31/11</td><td style="font-weight: bold; text-align: center; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(194, 214, 154);">04/30/11</td><td style="font-weight: bold; text-align: center; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(194, 214, 154);">05/31/11</td><td style="font-weight: bold; text-align: center; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(194, 214, 154);">06/30/11</td><td style="text-align: right; border-left: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">3</td><td style="font-weight: bold; text-align: center; border: 1px solid black; background-color: rgb(255, 255, 255);">Employee</td><td style="font-weight: bold; text-align: center; border: 1px solid black; background-color: rgb(255, 255, 255);">Emp ID</td><td style="font-weight: bold; text-align: center; border: 1px solid black; background-color: rgb(255, 255, 255);">
</td><td style="font-weight: bold; border: 1px solid black; background-color: rgb(255, 255, 255);">% Comp</td><td style="font-weight: bold; border: 1px solid black;">item 1</td><td style="font-weight: bold; border: 1px solid black;">item 2</td><td style="font-weight: bold; border: 1px solid black;">item 3</td><td style="font-weight: bold; border: 1px solid black;">item 4</td><td style="font-weight: bold; border: 1px solid black;">item 5</td><td style="font-weight: bold; border: 1px solid black;">item 6</td><td style="border-bottom: 1px solid black; border-left: 1px solid black; background-color: rgb(146, 208, 80);">nominator/denominator</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">4</td><td style="border: 1px solid black;">Fred</td><td style="text-align: right; border: 1px solid black;">63155</td><td style="text-align: center; border-top: 1px solid black; border-right: 1px solid black; border-left: 1px solid black; background-color: rgb(146, 208, 80);">100%</td><td style="text-align: center; border: 1px solid black;">NR</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">1/21/011</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">2/1/11</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">3/31/11</td><td style="text-align: center; border: 1px solid black;">3/15/11</td><td style="text-align: center; border: 1px solid black;">3/16/11</td><td style="text-align: center; border: 1px solid black;">3/17/11</td><td style="border: 1px solid black; background-color: rgb(146, 208, 80);">3/3</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">5</td><td style="border: 1px solid black;">Barnie</td><td style="text-align: right; border: 1px solid black;">122861</td><td style="text-align: center; border-right: 1px solid black; border-left: 1px solid black; background-color: rgb(146, 208, 80);">66%</td><td style="text-align: center; border: 1px solid black;">NR</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">1/21/012</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">
</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">4/1/11</td><td style="text-align: center; border: 1px solid black;">3/16/11</td><td style="text-align: center; border: 1px solid black;">
</td><td style="text-align: center; border: 1px solid black;">3/18/11</td><td style="border: 1px solid black; background-color: rgb(146, 208, 80);">2/3</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">6</td><td style="border: 1px solid black;">George</td><td style="text-align: right; border: 1px solid black;">82718</td><td style="text-align: center; border-right: 1px solid black; border-left: 1px solid black; background-color: rgb(146, 208, 80);">0%</td><td style="text-align: center; border: 1px solid black;">NR</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NH</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NH</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NH</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="border: 1px solid black; background-color: rgb(146, 208, 80);">NH = 0 </td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">7</td><td style="border: 1px solid black;">Jane</td><td style="text-align: right; border: 1px solid black;">137293</td><td style="text-align: center; border-right: 1px solid black; border-left: 1px solid black; background-color: rgb(146, 208, 80);">NR</td><td style="text-align: center; border: 1px solid black;">NR</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NR</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NR</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NR</td><td style="text-align: center; border: 1px solid black;">
</td><td style="text-align: center; border: 1px solid black;">NR</td><td style="text-align: center; border: 1px solid black;">NR</td><td style="border: 1px solid black; background-color: rgb(146, 208, 80);">NR = Not Counted = NR </td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">8</td><td style="border: 1px solid black;">Elroy</td><td style="text-align: right; border: 1px solid black;">72894</td><td style="text-align: center; border-right: 1px solid black; border-left: 1px solid black; background-color: rgb(146, 208, 80);">33%</td><td style="text-align: center; border: 1px solid black;">NR</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NH</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">2/1/11</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NH</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="border: 1px solid black; background-color: rgb(146, 208, 80);">1/3</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">9</td><td style="border: 1px solid black;">Judy</td><td style="text-align: right; border: 1px solid black;">72895</td><td style="text-align: center; border-right: 1px solid black; border-left: 1px solid black; background-color: rgb(146, 208, 80);">0%</td><td style="text-align: center; border: 1px solid black;">NR</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">
</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">
</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">
</td><td style="text-align: center; border: 1px solid black;">
</td><td style="text-align: center; border: 1px solid black;">
</td><td style="text-align: center; border: 1px solid black;">
</td><td style="border: 1px solid black; background-color: rgb(146, 208, 80);">0/3</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">10</td><td style="border: 1px solid black;">Astro</td><td style="text-align: right; border: 1px solid black;">72896</td><td style="text-align: center; border-right: 1px solid black; border-left: 1px solid black; background-color: rgb(146, 208, 80);">100%</td><td style="text-align: center; border: 1px solid black;">NR</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NR</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">2/3/11</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NR</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="border: 1px solid black; background-color: rgb(146, 208, 80);">1/1</td></tr></tbody></table>

Sheet2​

 

[Jerry Sullivan]

I should of mentioned I am using 2007 for the demonstration - but the formula will be put back on version 2003

That's funny. I had posted "If you have xl2007, you could try.." and then revised it when I saw xl2007 listed in your screen shot.

Well I'm not that familiar with the differences between xl2007 and xl2003, but I know one of them is that xl2003 doesn't have the IFERROR function.

To work around that, you could try:

=if(ISERROR(COUNTIFS(E$6:J$6,"<="&TODAY(),E10:J10,">0")/COUNTIFS(E$6:J$6,"<="&TODAY(),E9:J9,"<>NR")),"NR",COUNTIFS(E$6:J$6,"<="&TODAY(),E10:J10,">0")/COUNTIFS(E$6:J$6,"<="&TODAY(),E9:J9,"<>NR"))

If that doesn't work due to there being another incompatible function, I hope an xl2003 user will help modify the formula.

 

[Jerry Sullivan]

BTW, I was able to decipher your OP and the forumula I posted will give the results in Column C that you showed in your 2nd post if using xl2007.

I also assumed that you have a typo in Cells E4:E5 and those date values have an extra zero in them.

P.S. Just noticed that the cells in your example shifted from example 1 to example 2.
The formula I posted assumes Row 6 has the due dates - you would need to change the to Row 2 for your latest example

 

[lefty38]

wow, JS411 you are real close
thank you for your diligence

after putting in the formula - it looked like it was calculating the date values in the yellow cells and the date in in row 1

the yellow values are really just completion dates (string) of the data items
I could use any text value as a completion check mark - they just need to be counted as a string
(less "NR" Not Required)


the only date calculation is today and row 1
if the date in row one is less than today count it
as the denominator

so C3 is
D1:F1 are counted (less than today) = 3
D3:F3 = 3 3 values (that are not "NH";"NR")
C3 = 3/3 = 1 (100%)

C4 is
D1:F1 are counted (less than today) = 3
D4:F4 = 2 2 values (that are not "NH";"NR")
C4 = 2/3 = 66.6 (67%)

C7 is
D1:F1 are counted (less than today) = 3
D7:F7 = 2 2 values (1 "NH") + (1 item) + (1 "NR" not counted)
C7 = 2/3 (66.6)

I adjusted your formula to compare the date values and today in D1:I1 only
but i am not sure which '<="&TODAY()' functions to remove without breaking it

again hope this helps clarify the request thank you

=IF(ISERROR(COUNTIFS(D$1:I$1,"<="&TODAY(),D3:I3,">0")/COUNTIFS(D$3:I$3,"<="&TODAY(),D3:I3,"<>NR")),"NR",COUNTIFS(D$3:I$3,"<="&TODAY(),D3:I3,">0")/COUNTIFS(D$3:I$3,"<="&TODAY(),D3:I3,"<>NR"))

Excel 2007<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(224, 224, 240);" width="25px"><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style="background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">1</td><td style="font-weight: bold; text-align: center; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(255, 255, 255);">
</td><td style="font-weight: bold; text-align: center; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(255, 255, 255);">
</td><td style="font-weight: bold; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(194, 214, 154);">Due dt</td><td style="font-weight: bold; text-align: center; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(194, 214, 154);">01/31/11</td><td style="font-weight: bold; text-align: center; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(194, 214, 154);">02/28/11</td><td style="font-weight: bold; text-align: center; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(194, 214, 154);">03/15/11</td><td style="font-weight: bold; text-align: center; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(194, 214, 154);">04/10/11</td><td style="font-weight: bold; text-align: center; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(194, 214, 154);">04/11/11</td><td style="font-weight: bold; text-align: center; border: 1px solid black; color: rgb(0, 0, 255); background-color: rgb(194, 214, 154);">04/12/11</td><td style="text-align: right; border-left: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="font-weight: bold; text-align: center; border: 1px solid black; background-color: rgb(255, 255, 255);">Employee</td><td style="font-weight: bold; text-align: center; border: 1px solid black; background-color: rgb(255, 255, 255);">Emp ID</td><td style="font-weight: bold; border: 1px solid black; background-color: rgb(255, 255, 255);">% Comp</td><td style="font-weight: bold; text-align: center; border: 1px solid black;">data</td><td style="font-weight: bold; text-align: center; border: 1px solid black;">data</td><td style="font-weight: bold; text-align: center; border: 1px solid black;">data</td><td style="font-weight: bold; text-align: center; border: 1px solid black;">data</td><td style="font-weight: bold; text-align: center; border: 1px solid black;">data</td><td style="font-weight: bold; text-align: center; border: 1px solid black;">data</td><td style="border-bottom: 1px solid black; border-left: 1px solid black; background-color: rgb(146, 208, 80);">nominator/denominator</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">3</td><td style="border: 1px solid black;">Fred</td><td style="text-align: right; border: 1px solid black;">63155</td><td style="text-align: center; border: 1px solid black;">NR</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">X</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">X</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">X</td><td style="text-align: center; border: 1px solid black;">X</td><td style="text-align: center; border: 1px solid black;">
</td><td style="text-align: center; border: 1px solid black;">X</td><td style="border: 1px solid black; background-color: rgb(146, 208, 80);">3/3</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">4</td><td style="border: 1px solid black;">Barnie</td><td style="text-align: right; border: 1px solid black;">122861</td><td style="text-align: center; border: 1px solid black;">0%</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">Comp</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">
</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">Comp</td><td style="text-align: center; border: 1px solid black;">C</td><td style="text-align: center; border: 1px solid black;">
</td><td style="text-align: center; border: 1px solid black;">C</td><td style="border: 1px solid black; background-color: rgb(146, 208, 80);">2/3</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">5</td><td style="border: 1px solid black;">George</td><td style="text-align: right; border: 1px solid black;">82718</td><td style="text-align: center; border: 1px solid black;">0%</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NH</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NH</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NH</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="border: 1px solid black; background-color: rgb(146, 208, 80);">NH = 0 0 of 3</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">6</td><td style="border: 1px solid black;">Jane</td><td style="text-align: right; border: 1px solid black;">137293</td><td style="text-align: center; border: 1px solid black;">NR</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NR</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NR</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NR</td><td style="text-align: center; border: 1px solid black;">
</td><td style="text-align: center; border: 1px solid black;">NR</td><td style="text-align: center; border: 1px solid black;">NR</td><td style="border: 1px solid black; background-color: rgb(146, 208, 80);">NR = Not Counted = NR </td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">7</td><td style="border: 1px solid black;">Elroy</td><td style="text-align: right; border: 1px solid black;">72894</td><td style="text-align: center; border: 1px solid black;">0%</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NH</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">2006-01-30</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NR</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="border: 1px solid black; background-color: rgb(146, 208, 80);">2/3</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">8</td><td style="border: 1px solid black;">Judy</td><td style="text-align: right; border: 1px solid black;">72895</td><td style="text-align: center; border: 1px solid black;">0%</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">
</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">
</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">
</td><td style="text-align: center; border: 1px solid black;">
</td><td style="text-align: center; border: 1px solid black;">
</td><td style="text-align: center; border: 1px solid black;">
</td><td style="border: 1px solid black; background-color: rgb(146, 208, 80);">0/3</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">9</td><td style="border: 1px solid black;">Astro</td><td style="text-align: right; border: 1px solid black;">72896</td><td style="text-align: center; border: 1px solid black;">0%</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NR</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">hi mom</td><td style="text-align: center; border: 1px solid black; background-color: rgb(255, 255, 153);">NR</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="text-align: center; border: 1px solid black;">NH</td><td style="border: 1px solid black; background-color: rgb(146, 208, 80);">1/1</td></tr></tbody></table>

Sheet2​

 

[Jerry Sullivan]

There were only two places that the row 1 dates need to be referenced and you got the first one. With the second change it looks like this:

=IF(ISERROR(COUNTIFS(D$1:I$1,"<="&TODAY(),D3:I3,">0")/COUNTIFS(D$3:I$3,"<="&TODAY(),D3:I3,"<>NR")),"NR",COUNTIFS(D$1:I$1,"<="&TODAY(),D3:I3,">0")/COUNTIFS(D$3:I$3,"<="&TODAY(),D3:I3,"<>NR"))

I haven't tested, but I think that should work. Please let me know if it doesn't.

 

[Jerry Sullivan]

Just realized what you meant by the strings indicating completion.
The formula is looking for numeric values (including dates) to determine the Numerator. That part is D3:I3,">0"

Changing the completed value to any string other than NR would require a change in the formula. Please try out the formula first with the dates per the OP. If that works and you want to make it any strings other than NR, I will revise the formula.

 

[Jerry Sullivan]

Lefty38,
I reworked the formula using SUMPRODUCT. I think this will do what you want:

=IF(ISERROR(SUMPRODUCT(--(D$1:I$1<=TODAY()),--(D3:I3<>"NR"),--(D3:I3<>"NH"),--(D3:I3<>""))/SUMPRODUCT(--(D$1:I$1<=TODAY()),--(D3:I3<>"NR"))),"NR",SUMPRODUCT(--(D$1:I$1<=TODAY()),--(D3:I3<>"NR"),--(D3:I3<>"NH"),--(D3:I3<>""))/SUMPRODUCT(--(D$1:I$1<=TODAY()),--(D3:I3<>"NR")))

For Cell C7, your example shows 2/3...should that be 1/2 = 50% instead?

 

[lefty38]

yes to the C7 question - I missed that

As for the formula ray: I bow my head to you
for your patients working with me


now tomorrow at work
i will see if this will fit inside of the 2003 fx text area

and see if i can make a macro that will place it into
cell I11 (not included in this thread) and copy paste down to the last populated cell on the worksheet

Sub insert_percent()
'
' insert_percent Macro into column I
' and auto fill to the last row populated in worksheet

    Range("I11").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(SUMPRODUCT(--(R1C[1]:R1C[6]<=TODAY()),--(RC[1]:RC[6]<>""NR""),--(RC[1]:RC[6]<>""NH""),--(RC[1]:RC[6]<>""""))/SUMPRODUCT(--(R1C[1]:R1C[6]<=TODAY()),--(RC[1]:RC[6]<>""NR""))),""NR"",SUMPRODUCT(--(R1C[1]:R1C[6]<=TODAY()),--(RC[1]:RC[6]<>""NR""),--(RC[1]:RC[6]<>""NH""),--(RC[1]:RC[6]<>""""))/SUMPRODUCT(--(R1C[1]:R1C[6]<=TODAY()),--(RC[1]:RC[6]<>""NR"")))"
    Range("I11").Select
    Selection.AutoFill Destination: Range ("I11:I41"), Type:=xlFillDefault
    Range("I3:I41").Select
End Sub

Problem is i do not i know how to auto fill the formula to the last row populated in the spreadsheet

(which changes every time new data is copy and pasted in)