Unable to Sum cells in Excel

B

Brett Walters

Hello,

I am having issues trying to use the SUM function to
autosum a couple of cells that have If functions within
them. I am trying to do the following:

In Q12 I am trying to get the Sum of N12, O12 and P12. I
have only tried =SUM(N12:p12) as the function formatting
the cell as text, general, numeric etc but the total
always ends up being 0.00, I never get an addition of the
values in the other cells.

Each of the N, O and P cells has the following function
within it,

=IF(F12="","",IF(F12>=90%,"5",IF(F12>=85%,"4",IF
(F12>=78%,"3",IF(F12>=72%,"2",IF(F12<72%,"1"))))))

I am basically comparing scores that people have gotten
against measures they need to meet. This gives them a
1,2,3,4 or 5 rating. It is the rating that I am trying to
sum up.

If anyone could assist helping me get Q12 to add up the
values it should it would be much appreciated. I am fairly
new to the excel functions so it is most likely something
simple.

Thanks
 
J

JE McGimpsey

Remove the quotes from around the numbers in your formulae. "5" is text,
which SUM() ignores:

=IF(F12="","",IF(F12>=90%,5,IF(F12>=85%,4,IF
(F12>=78%,3,IF(F12>=72%,2,IF(F12<72%,1))))))
 
D

Dana DeLouis

Not sure, but would you want to experiment with the Match function:

=MATCH(F12,{0,0.72,0.78,0.85,0.9})
 
Top