How to Count Adults and Children Who Selected Yes in Excel
Question details
The user needs an Excel formula to count the total number of adults ('A') and children ('K') who selected 'Yes' in a separate response column.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Evaluating survey or demographic data where respondents are categorized into age groups and their responses to a specific question are recorded.
- Observed behavior
- The goal is to calculate a combined count using multiple criteria across two different columns (category and response).
Ensure your data is organized cleanly into separate columns, with one column containing the demographic categories (e.g., 'A' and 'K') and another containing the exact text responses (e.g., 'Yes').
Use COUNTIFS with Addition
You can calculate the total by using two separate COUNTIFS functions and adding their results together.
The COUNTIFS function allows you to count cells that meet multiple criteria. By creating one COUNTIFS formula for adults who answered 'Yes' and another for children who answered 'Yes', you can easily sum them up for the final total.
Click on the empty cell where you want the final count to appear.
Type =COUNTIFS(A2:A100,"A",B2:B100,"Yes") to count the adults.
Add a plus sign (+) and type the second formula: COUNTIFS(A2:A100,"K",B2:B100,"Yes"). The complete formula will be =COUNTIFS(A2:A100,"A",B2:B100,"Yes")+COUNTIFS(A2:A100,"K",B2:B100,"Yes").
Press Enter to execute the formula and get the combined total count of adults and children who selected Yes.
Use an Array Constant with SUM and COUNTIFS
For a shorter and cleaner formula, you can use an array constant to check for both 'A' and 'K' within a single COUNTIFS function.
Analyze Data Quickly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical formulas like COUNTIFS, SUMIFS, and array constants, making it easy to process complex survey responses and demographic datasets.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your survey or category data.
- 2. Select an output cell: Click on an empty cell to place your calculation result.
- 3. Input the COUNTIFS formula: Type your =COUNTIFS combination formula to evaluate your category and response columns.
- 4. Get the result: Press Enter to instantly process the data and display your total count.
Frequently Asked Questions
Why is my COUNTIFS formula returning a #VALUE! error?
A #VALUE! error in COUNTIFS usually occurs when the ranges you are comparing are not the exact same size. Ensure that both your category range (e.g., A2:A100) and response range (e.g., B2:B100) have the same number of rows.
Can I count cells that do not equal 'Yes'?
Yes. To count responses that are not 'Yes', use the not equal operator '<>' in your formula. Change the criteria from "Yes" to "<>Yes".
Does this formula work if my cells contain extra spaces?
Extra spaces (like 'Yes ' instead of 'Yes') will cause the formula to skip those cells, returning an inaccurate count. You can use the TRIM function on your data column to remove extra spaces, or use wildcard characters like "*Yes*" in the formula.
How do I count data located on a different worksheet?
You can reference ranges on other sheets by adding the sheet name followed by an exclamation mark before the range. For example, use Sheet2!A2:A100 as your range in the COUNTIFS formula.
