Summing highest value in cells limited by validation.

[dci1812]

Good day. I have a sheet used to track scores on an assessment. I have limited the possible cell entries (using the validation function) to either full points awarded, zero points awarded, or NA. I would like to be able to sum all the cells that have either full points or zero points, BUT for the cells with zero points awarded, I would need the value counted to be what the full points awarded would have been (if they hadn't gotten a zero). Basically, I am trying to get the total points possible (minus any NA's). I will try to draw an example.<BR><BR>
<TABLE style="WIDTH: 500px" class="wysiwyg_dashes wysiwyg_cms_table_grid">
<TBODY>
<TR class="wysiwyg_dashes_tr wysiwyg_cms_table_grid_tr" vAlign=top>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P><FONT color=#ff0000>A</FONT></P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P><FONT color=#ff0000>B</FONT></P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P><FONT color=#ff0000>C</FONT></P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P><FONT color=#ff0000>D</FONT></P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P><FONT color=#ff0000>E</FONT></P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P><FONT color=#ff0000>F</FONT></P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P><FONT color=#ff0000>G</FONT></P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P><FONT color=#ff0000>Points awarded</FONT></P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P><FONT color=#ff0000>Possible points</FONT></P></TD></TR>
<TR class="wysiwyg_dashes_tr wysiwyg_cms_table_grid_tr" vAlign=top>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P><FONT color=#ff0000>1</FONT></P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>1</P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>0</P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>NA</P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>5</P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>0</P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>5</P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>5</P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>16</P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>24</P></TD></TR>
<TR class="wysiwyg_dashes_tr wysiwyg_cms_table_grid_tr" vAlign=top>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P><FONT color=#ff0000>2</FONT></P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>0</P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>5</P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>3</P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>0</P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>3</P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>0</P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>NA</P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>11</P></TD>
<TD class="wysiwyg_dashes_td wysiwyg_cms_table_grid_td" align=middle>
<P>22</P></TD></TR></TBODY></TABLE><BR><BR>So, for example, cell A:1 has been limited to an entry of 1, 0, or NA, cell B:1 has been limited to an entry of 5, 0, or NA. I am trying to have the "Possible Points" cells sum the total POSSIBLE points available (not counting the NA cells).<BR><BR>Any help would be most appreciated.

 

[Marcelo Branco]

Maybe...

=SUMPRODUCT({1,5,3,5,3,5,5},--ISNUMBER(A1:G1))

Hope this helps

M.

 

[dci1812]

Maybe...

=SUMPRODUCT({1,5,3,5,3,5,5},--ISNUMBER(A1:G1))

Hope this helps

M.

Marcelo, this worked PERFECTLY! Thank you very much!

 

[Marcelo Branco]

Marcelo, this worked PERFECTLY! Thank you very much!

You are welcome. Thanks for the feedback.

M.