MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do you add the value of a range that contains formulas?


Posted by Lynne on September 24, 2001 11:11 AM

I'm trying to get a column of cells to add and each cell in the column contains a formula. So I want the add function to only recognize the value in each cell instead of the formula. Now I can get it to work if I list each cell in the formula like this: =value(b2+b4)
but since there are 50 cells I need to add I want to put in the range. However, when I do this =value(b2:b52) I get this error:#value!
Can anyone help me? Please??!


Posted by Aladin Akyurek on September 24, 2001 11:13 AM

Lynne,

Just use

=SUM(B2:B52)

Aladin

Posted by Lynne on September 24, 2001 11:19 AM

That doesn't work with formulas


When I do that it gives me a return of 0 even though there are numbers in the cells. I believe that with that fx it sees the formulas in each cell as text and doesn't recognize the values listed. Do you have another suggestion?


Posted by Aladin Akyurek on September 24, 2001 11:24 AM

Re: That doesn't work with formulas


Sum would ignore text-formatted numbers but it shouldn't any problems with numbers produced by formulas.

Try the following:

=SUMPRODUCT((B2:B52)+0)

Posted by Lynne on September 24, 2001 11:34 AM

Yes, that worked!!

THANK YOU! THANK YOU! THANK YOU! THANK YOU!