'%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 Count Cells Containing H or S in Excel
Question details
The user needs to count specific cells containing the text 'H' or 'S' while ignoring blanks or other characters.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing worksheet data that includes multiple specific text values, requiring a count of cells that match one of several given criteria.
- Observed behavior
- The user wants to evaluate a specific range of cells and return the total count of cells that exactly match either 'H' or 'S'.
Identify the exact range of cells you want to evaluate and ensure there are no hidden trailing spaces in the cells containing 'H' or 'S', as extra spaces can prevent the formula from recognizing the text.
Use Multiple COUNTIF Functions
The most straightforward method is to use the COUNTIF function twice (once for each text criteria) and add the results together.
This method is simple to understand and works perfectly when you only have a few specific text values to count.
Click on the empty cell where you want the final count to be displayed.
Type the formula =COUNTIF(A1:A10,"H")+COUNTIF(A1:A10,"S") into the formula bar. Replace A1:A10 with the actual range containing your data.
Press the Enter key. The cell will now display the total combined number of cells containing exactly 'H' or 'S'.
Use SUM and COUNTIF with an Array Constant
For a more compact formula, you can use an array constant inside the COUNTIF function wrapped with SUM.
Count Data Easily with WPS Spreadsheet
You can perform all advanced data counting tasks, including COUNTIF and array formulas, directly in WPS Spreadsheet. It offers a familiar interface and comprehensive function support for all your data analysis needs.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet document containing the data you want to count.
- 2. Apply the counting formula: Select a blank cell and input the exact same formula you use in Excel: =SUM(COUNTIF(A1:A10,{"H","S"})).
- 3. Press Enter to calculate: Hit Enter to instantly view the counted cells. WPS Spreadsheet seamlessly handles array constants and standard Excel functions.
Frequently Asked Questions
Why is my COUNTIF formula returning 0?
This usually happens if the target cells contain hidden characters or trailing spaces. You can use the TRIM function in a helper column to clean your data first, or check if your selected cell range in the formula is correct.
Can I count more than two specific texts using this method?
Yes. With the array method, you can easily add more criteria inside the curly brackets. For example, to count H, S, and X, you would use =SUM(COUNTIF(A1:A10,{"H","S","X"})).
Are these counting formulas case-sensitive?
No, the COUNTIF function is not case-sensitive. It will count 'H' and 'h' as the same character. If you require strict case-sensitive counting, you must use a combination of the SUMPRODUCT and EXACT functions, such as =SUMPRODUCT(--(EXACT(A1:A10,"H"))).
