'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Convert a Text String into a Working SUM Formula in Excel
Question details
The user needs to convert a concatenated text string that represents a range into an active SUM formula, specifically for dynamically summing repeating 7-row data blocks.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating dynamic sum formulas across multiple repeating blocks of worksheet data.
- Observed behavior
- The CONCATENATE function outputs the SUM expression as a static text string rather than calculating it as a mathematical formula.
Ensure that the sheet name and range references in your text string perfectly match the actual worksheet data to avoid #REF! errors when converting the string into a formula.
Use the INDIRECT Function to Evaluate Text as a Range Reference
The INDIRECT function converts a text string into a valid Excel reference, allowing the SUM function to correctly calculate the values within that specific text-defined range.
When you build a formula using CONCATENATE, Excel treats the final output as plain text. Wrapping that text string in the INDIRECT function tells Excel to evaluate it as a spatial cell reference instead.
Click on the cell where you want the final SUM calculation to appear.
Type the formula =SUM(INDIRECT("'Store Compiled'!F18:F24")) to evaluate the specific text string as an active range formula.
For repeating 7-row data blocks, combine INDIRECT with the ROW function to dynamically update the range. Enter =SUM(INDIRECT("'Store Compiled'!F" & (ROW(A1)-1)*7+18 & ":F" & (ROW(A1)-1)*7+24)).
Press Enter to calculate the formula, then click and drag the fill handle down to apply the dynamic calculation to the rest of your summary table.
Calculate Dynamic Text Formulas Easily with WPS Spreadsheet
WPS Spreadsheet fully supports the INDIRECT, SUM, and ROW functions, making it simple to convert text strings into working dynamic formulas for complex data blocks.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your text string formulas.
- 2. Select the formula cell: Click on the cell where you need the dynamic sum to be calculated.
- 3. Input the INDIRECT function: Type =SUM(INDIRECT(your_text_reference)) wrapping your dynamic row logic.
- 4. Calculate and apply: Press Enter to instantly calculate the text as an active formula, then drag down to fill.
Frequently Asked Questions
Why is my Excel formula showing as text instead of calculating?
If the cell format is set to 'Text' before you enter the formula, or if you use functions like CONCATENATE to build the expression, the software treats the input as a literal string. You must use INDIRECT to evaluate text references, or change the cell format to 'General' and press F2 then Enter.
Can I use the EVALUATE function in Excel to convert text strings to formulas?
Yes, but EVALUATE is a legacy macro function. It cannot be used directly in standard worksheet cells. You must define a Named Range that uses EVALUATE and then refer to that Named Range in your target cell.
How do I dynamically sum rows that are grouped in blocks of 7?
You can use a combination of the OFFSET or INDIRECT function alongside the ROW function to create a dynamic range. Multiplying the ROW result by 7 (e.g., ROW(A1)*7) allows you to automatically jump 7 rows down for each sequential cell you drag the formula into.
