Adding Up Values Only in Excel

Ignore formula results

Here is the problem – you have a list of values which includes sub-totals calculated using SUM functions. You only want add up the values and ignore the SUM function cells.

I am assuming the SUBTOTAL function has not been used. See image below for the layout. Cells B11 and B17 have SUM functions. We don’t want to add these cells up.

Valuesonly

In Excel 2013 a new function was added called FORMULATEXT. This function returns an error if there is no formula. We can use this function to identify just the values within a range. The formula in cell D2 is

=SUMPRODUCT((ISERROR(FORMULATEXT(B2:B19))*(B2:B19)))

As mentioned the FORMULATEXT function returns an error for cells without a formula. Value cells are not formula cells. Hence, the ISERROR function will return TRUE for value cells. The TRUE is converted to 1 and multiplied by the corresponding values and the values added up. The SUM function cells return FALSE with the ISERROR and are converted to zero which zeroes their respective values.

NOTE: If a cell has a value that has been entered as a formula eg =10+5 then it will not be included in the about formula total. =10+5 is treated as a formula and not summed.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.