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.

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.