'%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 Yes Values in Nonadjacent Excel Cells
Question details
The user wants to count the number of occurrences of a specific word (such as Yes) across multiple, non-contiguous drop-down cells.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating exact totals from specific drop-down menus that are scattered across non-contiguous cells in a spreadsheet.
- Observed behavior
- A formula is needed to evaluate specific isolated cells (e.g., C7, C9, C11) without inadvertently counting the unrelated cells located between them.
Verify that your drop-down menus are correctly configured and that the target text 'Yes' does not contain hidden trailing spaces, which can cause formula matching errors.
Add Multiple COUNTIF Functions Together
The most straightforward method for a small number of scattered cells is to add individual COUNTIF functions together in a single formula.
Click on the cell where you want the final count to be displayed.
Type the formula: =COUNTIF(C7,"Yes")+COUNTIF(C9,"Yes")+COUNTIF(C11,"Yes"). Replace the cell references with the specific nonadjacent cells you need to evaluate.
Press the Enter key. Excel will evaluate each cell individually and sum the results to give you the total count of 'Yes' values.
Use SUMPRODUCT for Alternating Rows
If your nonadjacent cells follow a strict alternating pattern (e.g., every odd row), SUMPRODUCT is much more efficient for evaluating large data ranges.
Evaluate the Entire Continuous Range
If you are certain that the cells located between your target drop-downs will never contain the word 'Yes', you can safely evaluate the entire continuous range.
Easily Manage Complex Formulas with WPS Spreadsheet
WPS Spreadsheet provides a robust and user-friendly platform for data analysis, offering full support for COUNTIF, SUMPRODUCT, and all standard Excel functions to help you count values across complex data ranges.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your drop-down menus.
- 2. Select an output cell: Click on the specific cell where you want the final Yes count to appear.
- 3. Input the formula: Type your COUNTIF or SUMPRODUCT formula exactly as you would in standard spreadsheet software.
- 4. Execute the calculation: Press Enter to instantly view your precise data count without compatibility issues.
Frequently Asked Questions
Can I use COUNTIF with multiple non-contiguous ranges directly?
No, the standard COUNTIF function in Excel only accepts a single continuous range as its first argument. To evaluate multiple separate ranges, you must either add several COUNTIF functions together or use more advanced array formulas.
Why is my COUNTIF formula returning 0 when I can clearly see 'Yes' in the cells?
This usually happens due to hidden trailing or leading spaces in the target cells (e.g., 'Yes '). You can use the TRIM function to clean your data, or ensure the data validation drop-down list is strictly formatted to eliminate extra spaces.
Is there a way to count cells if they equal 'Yes' or 'No'?
Yes, you can sum two COUNTIF statements together, such as =COUNTIF(C7:C11,"Yes")+COUNTIF(C7:C11,"No"). Alternatively, for continuous ranges, you can use an array formula like =SUM(COUNTIF(C7:C11,{"Yes","No"})).
