'%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 Use Absolute Cell References in Excel COUNTIF Formulas
Question details
The user needs to correctly apply absolute cell references within a COUNTIF formula to lock the range when copying it, and to resolve syntax errors in their current formula.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Applying a COUNTIF formula across multiple cells without the reference range shifting down or across.
- Observed behavior
- The formula =COUNIF($B2:$B9,”MA”) fails due to misspelled function names, incorrect quotation marks, and mixed references instead of fully locked absolute references.
Double-check that your function name is spelled correctly (COUNTIF instead of COUNIF) and ensure you are using standard straight quotes ("") rather than smart quotes (“”) for your criteria.
Use the F4 Key to Apply Absolute References
The quickest way to lock a cell range in Excel is by using the F4 keyboard shortcut while editing your formula.
Using absolute references ensures that when you copy a formula down a column or across a row, the specified range remains exactly the same. The dollar signs ($) indicate that the row and column are locked.
Click the cell where you want to enter or edit your COUNTIF formula.
Type =COUNTIF( and use your mouse to highlight the desired cell range, for example, B2:B11.
Immediately after selecting the range, press the F4 key on your keyboard. This changes the relative reference (B2:B11) to an absolute reference ($B$2:$B$11).
Type a comma, enter your criteria in straight quotes (e.g., "CO"), close the parentheses, and press Enter.
Manually Type Dollar Signs for Absolute References
If the F4 key shortcut is unavailable or unresponsive on your keyboard, you can manually type dollar signs to lock the rows and columns.
Master Formulas Easily with WPS Spreadsheet
WPS Office provides a powerful, fully compatible spreadsheet tool that perfectly supports standard Excel formulas like COUNTIF. You can use identical syntax and keyboard shortcuts to perform complex data analysis efficiently.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
- 2. Start the formula: Select an empty cell and type =COUNTIF( to begin the calculation.
- 3. Lock the range with F4: Highlight your target range using the mouse, then press F4 to automatically add the $ signs for an absolute reference.
- 4. Finish and apply: Enter your condition in quotation marks, close the bracket, and press Enter. Drag the formula down to apply it seamlessly.
Frequently Asked Questions
What is the difference between relative, absolute, and mixed references in Excel?
Relative references (like A1) change based on the relative position when a formula is copied. Absolute references (like $A$1) remain strictly locked to a specific cell. Mixed references lock either the row (A$1) or the column ($A1).
Why is my COUNTIF formula returning a #NAME? error?
This error usually occurs if the function name is misspelled. For instance, typing 'COUNIF' instead of the correct 'COUNTIF' will prompt Excel to return a #NAME? error because it does not recognize the command.
How do I toggle through different reference types using F4?
When you highlight a cell reference in the formula bar and press F4 repeatedly, Excel cycles through all variations: absolute ($A$1), mixed row (A$1), mixed column ($A1), and back to relative (A1).
Why does my COUNTIF formula show an error when using quotation marks?
Excel formulas require standard straight quotation marks ("") for text criteria. If you copied a formula from a word processor or website, it might contain smart or curly quotes (“”), which Excel cannot process correctly. Replace them with straight quotes.
