Need help with an excel formula

[Rose Rodney]

I'm trying to calculate scores on a spreadsheet. These scores when accumulated totals100. However there are instances when a score criteria may have equal a not applicable value, this should not affect the overall end score. Example 25+25+25+n/a+25=75/75 or 100%. Is there a way for me a achieve this formula?

 

[David Evans]

In B7 of the attached worksheet the formula is entered Ctrl-Shift-Enter ...
There is something awry with your description - I don't think
"25+25+25+n/a+25=75/75" is correct, but should be 100/100?

 

[NARAYANK991]

Hi ,

When you add cell values using the + sign , then there is a problem with text values.

However , if you use the SUM function , this ignores text values and adds only the numbers.

Thus , if your values have the text string N/A , it will be ignored by the SUM function ; error values such as #N/A will still be a problem. For such values , you will have to use the technique posted by Evans.

Narayan

 

[Khalid NGO]

Hi @Rose Rodney welcome to the forum

Using David's file, another option (normal entered formula), if there is no minus value:
=SUMIF(A2:A6,">0")

and this, if there are minus values:

=SUM(SUMIF(A2:A6,{">0","<0"}))

Regards,

 

[NARAYANK991]

Hi ,

Another option , especially if you use Excel 2010 and later versions , is to use the AGGREGATE function.

Narayan

 

[ANKUSHRS1]

you may use below formula if #N/A is your problem.

=SUMIF(A2:A6,"<>#N/A")