How to Sum Only Cells Containing Formulas in Excel
Question details
The user wants to calculate the sum of values within a specific range, but only for cells that contain formulas, ignoring manually entered hardcoded values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Adding up calculated totals or sub-totals in a mixed data set without double-counting static values or constants.
- Observed behavior
- Standard SUMIF cannot evaluate the ISFORMULA condition directly as a criterion, requiring an array formula or SUMPRODUCT workaround to achieve the correct sum.
Identify the specific range of cells you want to evaluate. Note that the ISFORMULA function is only available in Excel 2013 and later versions.
Use SUMPRODUCT and ISFORMULA
This is the most straightforward method as it handles arrays natively without needing special keyboard shortcuts.
The SUMPRODUCT function can evaluate arrays seamlessly. By multiplying the boolean result of ISFORMULA by the cell's actual value, it isolates and sums only the formula-driven results.
Click on the empty cell where you want the final sum to appear.
Type the formula =SUMPRODUCT(ISFORMULA(E18:E60)*E18:E60) into the formula bar. Replace E18:E60 with your actual data range.
Press the Enter key. The function will multiply TRUE (1) or FALSE (0) by the cell values, summing only the formula cells.
Use an Array Formula with SUM and ISFORMULA
An alternative method using the standard SUM function combined with an IF statement, entered as an array formula.
Easily Sum Formula Cells with WPS Spreadsheet
WPS Spreadsheet provides full support for advanced functions like SUMPRODUCT and ISFORMULA, allowing you to easily handle complex data calculations. Best of all, it offers a seamless, highly compatible alternative to Microsoft Excel.
- 1. Open your workbook in WPS: Launch WPS Office and open your .xlsx file containing the mixed data.
- 2. Type the SUMPRODUCT formula: Select an empty cell and input =SUMPRODUCT(ISFORMULA(A1:A10)*A1:A10), replacing the range with your own.
- 3. Calculate the result: Press Enter to instantly sum only the cells generated by formulas.
Frequently Asked Questions
Why doesn't SUMIF work directly with ISFORMULA?
The SUMIF function requires a range and a specific criterion string or logical operator. It cannot natively evaluate another function's array output (like ISFORMULA) within its criteria argument. This limitation makes SUMPRODUCT or array formulas necessary.
What happens if my formula cells contain errors?
If any of the formula cells within your targeted range evaluate to an error (such as #DIV/0! or #VALUE!), the SUMPRODUCT function will also return an error. You should wrap your original data formulas in the IFERROR function to prevent calculation breaks.
How can I sum cells that do NOT contain formulas?
You can reverse the logic by incorporating the NOT function. For example, use the formula =SUMPRODUCT(NOT(ISFORMULA(E18:E60))*E18:E60) to sum only the manually entered, hardcoded values in that specific range.
