Is there a formula that will sum ONLY cells that contain a value (not cells with a formula)? For example, say in column A of a spreadsheet I have a mixture of entered values and formulas that return values. If I use a sum formula at the end it will naturally sum all of the numbers in the selected array regardless of whether they are entered values or values resulting from a formula. (Maybe some kind of SUMIF & VBA code combo..) In case my description wasn't clear, here is a hypothetical spreadsheet set-up where i would need this formula:
A
1| 400
2| =SUM(B1:B3)
3| =AVERAGE(B1:B3)
4| 200
5| 100
6| =COUNT(B1:B3)
7| I want the sum Formula in this cell (A7) to return the value 700 (the sum of the values above).
Best Answer
If you use SUBTOTAL for all of your functions, you can do it. SUBTOTAL will ignore any other SUBTOTAL functions in the range. In A2
In A3
In A6
In A7
A7 will be 700 (which is what I assume you meant). If you have formulas that aren't an option in SUBTOTAL, then it won't work.