Range.Calculate always calculates DATEVALUE using MMDDYY format, and does not calculate conditional formats. Although Excel 2000 SR1 is documented as containing a fix for Range Calculate it still works the same way as above. In Automatic Mode: Range Calculate triggers an an automatic recalculation but does not itself explicitly calculate the. Select a cell below or to the right of the numbers for which you want to find the smallest number. On the Home tab, in the Editing group, click the arrow next to AutoSum, click Min (calculates the smallest) or Max (calculates the largest), and then press ENTER. If the cells are not in a contiguous row or column.
-->Note
Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. For more information about this change, read this blog post.
Summary
![How to calculate range in excel How to calculate range in excel](/uploads/1/1/7/8/117836917/913906306.jpg)
This article contains and describes formulas that calculate the following:
- The number of occurrences of a text string in a range of cells.
- The number of occurrences of a character in one cell.
- The number of occurrences of a character in a range of cells.
- The number of words (or text strings) separated by a character in a cell.
More Information
Formula to Count the Number of Occurrences of a Text String in a Range
=SUM(LEN(range)-LEN(SUBSTITUTE(range,'text',')))/LEN('text')
Where range is the cell range in question and 'text' is replaced by the specific text string that you want to count.
Note
The above formula must be entered as an array formula. To enter a formula as an array in Excel for Windows, press CTRL+SHIFT+ENTER. To enter a formula as an array in Excel for Macintosh, press COMMAND+RETURN.
The formula must be divided by the length of the text string because the sum of the character length of the range is decreased by a multiple of each occurrence of the text string. This formula can replace all later formulas in this article except the formula to count the number of words in a cell.
Example 1: Counting the Number of Occurrences of a Text String in a Range
- Start Excel, and then open a new workbook.
- Type the following on sheet1:The value of cell A8 is 4 because the text 'apple' appears four times in the range.
Formula to Count the Number of Occurrences of a Single Character in One Cell
=LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,'a','))
Where cell_ref is the cell reference, and 'a' is replaced by the character you want to count.
Note
This formula does not need to be entered as an array formula.
Example 2: Counting the Number of Occurrences of a Character in One Cell
Use the same data from the preceding example; assuming you want to count the number of occurrences of the character 'p' in A7. Type the following formula in cell A9:
A9: =LEN(A7)-LEN(SUBSTITUTE(A7,'p','))
The value of cell A9 is 3 because the character 'p' appears three times in A7.
Formula to Count the Number of Occurrences of a Single Character in a Range
=SUM(LEN(range)-LEN(SUBSTITUTE(range,'a',')))
Where range is the cell range in question, and 'a' is replaced by the character you want to count.
Note
The above formula must be entered as an array formula. To enter a formula as an array formula in Excel, press CTRL+SHIFT+ENTER.
Example 3: Counting the Number of Occurrences of a Character in a Range
Use the same data from the preceding example; assuming you want to count the number of occurrences or the character 'p' in A2:A7. Type the following formula in cell A10:
A10: =SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,'p',')))
Note
The above formula must be entered as an array formula. To enter a formula as an array formula in Excel, press CTRL+SHIFT+ENTER.
The value of cell A10 is 11 because the character 'p' appears 11 times in A2:A7.
Formula to Count the Number of Words Separated by a Character in a Cell
=IF(LEN(TRIM(cell_ref))=0,0,LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,char,'))+1)
Where cell_ref is the cell reference, and char is the character separating the words.
Note
There are no spaces in the above formula; multiple lines are used only to fit the formula into this document. Do not include any spaces when you type it into the cell. This formula does not need to be entered as an array formula.
Example 4: Counting the Number of Words Separated by a Space in a Cell
To count the number of words in a cell where the words are separated by a space character, follow these steps:
- Start Excel, and then open a new workbook.
- Type the following on sheet1:
The formula in cell A2 returns a value of 4 to reflect that the string contains four words separated by spaces. If words are separated by multiple spaces or if words start or end in a space, it does not matter. The TRIM function removes extra space characters and starting and ending space characters in the text of the cell.
In Excel, you can also use a macro to count the occurrences of a specific character in a cell, or range of cells.
References
For additional information about counting occurrences of text, click the following article number to view the article in the Microsoft Knowledge Base:
89794 How to use Visual Basic for Applications to count the occurrences of a character in a selection in Excel
Calculation Methods
For information on the calculation methods you can use from VBA see Calculating from VBA
Full Calculation and Re-Calculation.
Normally Excel calculates each cell as you enter it, and minimises calculation by only recalculating all the dependents of all the changed cells in all the open Workbooks.
You can request a full calculation (all formulae) by pressing Ctrl-Alt-F9, or a recalculation (all new/changed/volatile formulae, cells and their dependents) by pressing F9.
Usually (but not always) a recalculation is faster than a full calculation.
In Excel 2002 you can request a full calculation with dependency tree rebuild by pressing Ctrl-Alt-Shift-F9.
You can request a full calculation (all formulae) by pressing Ctrl-Alt-F9, or a recalculation (all new/changed/volatile formulae, cells and their dependents) by pressing F9.
Usually (but not always) a recalculation is faster than a full calculation.
In Excel 2002 you can request a full calculation with dependency tree rebuild by pressing Ctrl-Alt-Shift-F9.
Sheet Calculation, Range Calculation, Formula and Part Formula Calculation.
Excel has finer levels of calculation than just the workbook:
Recalculate Selected Worksheet(s) (Shift-F9)
Calculate Worksheet usually flags the Workbook as uncalculated in Manual mode. | In Manual Mode:Recalculates only the uncalculated and volatile cells on the selected worksheet in dependency chain sequence, or if multiple worksheets are selected they are all calculated. Note that this only gives you 'correct' results if all precedent inter-sheet and inter-workbook dependencies have already been fully calculated, and that cells on other worksheets that are dependent on the sheet(s) are not recalculated. After a sheet calculate the formulae in the sheet, the sheet and the workbook are flagged as uncalculated (CALCULATE shows in the statusbar), unless there were no uncalculated or volatile cells. Sheet Calculate does NOT reset dirtied/uncalculated cells as calculated after calculating them: the second of two successive Shift-F9 sheet recalculates in Manual mode will calculate exactly the same cells as the first. In Automatic Mode:Recalculates all uncalculated and volatile cells on the selected worksheet, or if multiple worksheets are selected they are all calculated. If any cells were recalculated then flags the workbook as uncalculated, which triggers an automatic recalculation to recalculate any dependents on other sheets and all volatile cells and their dependents on ALL worksheets, not just the selected sheet(s). If there are no uncalculated dependents on other sheets or volatile cells then nothing happens in the triggered automatic calculation because the workbook was already calculated. |
How To Calculate Range Using Excel
Calculate Selected Range (using Range Calculate in VBA)
Calculate Range resets the calculation sequence for the selected range, and flags the calculated cells as uncalculated. | In Manual Mode:Calculates all the cells in the selected range left-to-right and top-to-bottom. Excel 97 and Excel 2000 ignore dependencies within the selected range, but Excel 2002 and Excel 2003 follow the left-to-right and top-to-bottom calculation with a dependency recalculation of the selected range only. This means that range.calculate can be significantly slower in Excel 2002 and 2003 than in Excel 97 and 2000, and will give different results if there are dependencies within the range being calculated that are not left-to-right and top-to-bottom. Excel 2007 has two Range Calculate methods: Range.Calculate, which works the same way as Excel 2002/2003, and Range.CalculateRowMajorOrder, which works the same way as Excel97/2000. Dependents and volatile cells which are outside the range are ignored. If one of the selected cells is part of a multi-cell array formula, then all the cells in the multi-cell array formula are calculated.
Range.Calculate always calculates DATEVALUE using MMDDYY format, and does not calculate conditional formats. Although Excel 2000 SR1 is documented as containing a fix for Range Calculate it still works the same way as above. In Automatic Mode:Range Calculate triggers an an automatic recalculation but does not itself explicitly calculate the cells referred to by the range.calculate. If there are no volatile cells then no calculations are done because the workbook was already calculated. If there are any volatile cells then ALL the volatile cells and their dependents on ALL worksheets are recalculated. Excel 2002/2003 Range Calculate Problems:Range Calculate will fail in Excel 2002/2003 if any of these conditions is true:
If the range you calculate includes a multi-cell array formula and you subsequently recalculate, the cells in the multi-cell array formula will be individually calculated. |
Calculating a Single Formula or Part Formula or Array Formula
How To Calculate Range In Excel
Select the formula in the Formula Bar or highlight a part of the formula in the Formula Bar and press F9.
The formula or part formula is replaced by the result.
For an array formula you will see an array of results, which is a wonderful way of debugging array formulae!
If you press Ctrl-Z (Undo) or Esc then the formula re-appears, but if you press Enter the formula or part formula is permanently replaced.
The formula or part formula is replaced by the result.
For an array formula you will see an array of results, which is a wonderful way of debugging array formulae!
If you press Ctrl-Z (Undo) or Esc then the formula re-appears, but if you press Enter the formula or part formula is permanently replaced.
Re-entering the = sign on the front of a block of formulae will make Excel recalculate them in a similar way to Range.calculate, except that the dependency tree for the formulae will also be rebuilt and in Excel2002 and 2003 the dependency recalculation of the range will not be done in Manual mode.
Step-by Step Formula Evaluation in Excel 2002/2003
Excel 2002 & 2003 has an Evaluate Formula button that allows you to watch as Excel steps through calculating a formula in the selected cell.