'%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 Values by Person in an Excel Matrix
Question details
The user wants to count specific text entries (like 'x' and 'c') for each person across multiple weekly columns in a matrix, avoiding the need to manually select individual column ranges.
- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Summarizing data from a complex matrix where columns are grouped by individuals' names, and specific values need to be counted dynamically.
- Observed behavior
- Counting values manually requires selecting disjointed columns for each person, which is time-consuming and prone to errors. The user needs an automated formula to handle the cross-referencing.
Ensure your spreadsheet software is updated to a version that supports dynamic array functions like BYCOL and LAMBDA. If you are using an older version, you will need to rely on legacy array formulas instead.
Use a Dynamic Array Formula with BYCOL and LAMBDA
This is the most efficient method for modern spreadsheet versions, allowing you to iterate over columns dynamically and count values based on multiple criteria.
By combining BYCOL and LAMBDA, you can create a custom function that evaluates each column in your matrix individually, checking for both the target person and the required entry.
Locate your matrix data range (e.g., $B$2:$G$8) and ensure your summary table has reference cells for the person's name (e.g., B$11) and the value to count (e.g., $A12).
Select the first output cell in your summary table and input the formula: =SUM(BYCOL($B$2:$G$8,LAMBDA(c,COUNTIF(c,B$11)*(COUNTIF(c,$A12)))))
Press Enter to calculate the result. Then, click the bottom-right corner of the cell and drag the fill handle to copy the formula across the rest of your summary table.
Use an Array Formula with SUM and IF
If your software does not support dynamic array functions, you can use a traditional array formula combining SUM and IF to achieve the same result.
Use WPS Spreadsheet to Manage Complex Matrix Formulas
WPS Spreadsheet is a powerful, lightweight tool for data analysis. It fully supports advanced matrix calculations, complex array formulas, and cross-referencing, helping you summarize extensive data effortlessly.
- 1. Open your Dataset: Launch WPS Spreadsheet and open the workbook containing your matrix data.
- 2. Input the Matrix Formula: Select the target summary cell and enter your array formula (e.g., using SUM and IF logic) to count values dynamically.
- 3. Fill the Summary Table: Press Enter and drag the fill handle across your table to instantly calculate all required counts for each person.
Frequently Asked Questions
Why is my BYCOL or LAMBDA formula returning a #NAME? error?
This error typically occurs if you are using a legacy version of Excel or spreadsheet software that does not support dynamic array functions. Try updating your software to the newest version, or use the alternative SUM(IF(...)) array formula.
Can I use COUNTIFS instead of combining COUNTIF with LAMBDA?
The standard COUNTIFS function requires all criteria ranges to be the same size and shape. It cannot natively iterate over a 2D matrix while dynamically comparing headers without the help of helper rows or modern functions like BYCOL.
Does WPS Office support dynamic array formulas?
Yes, the latest versions of WPS Spreadsheet include support for numerous advanced dynamic array formulas. Ensure your WPS Office is updated to the most recent version to utilize functions like BYCOL and LAMBDA.
