How to Count Matching Last Digits in Excel Using SUMPRODUCT
Question details
Count the number of rows that meet two conditions: an exact text match for a category, and a numerical match based strictly on the final digit of numbers in another column.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing a dataset where one needs to tally occurrences based on a specific category string and a partial numerical value, specifically extracting the final digit to compare against a target.
- Observed behavior
- A standard counting formula is required to evaluate multiple criteria, integrating the MOD function within an array-processing formula like SUMPRODUCT.
Ensure your data ranges are identical in size and verify that the column designated for the MOD function contains only numerical values, as blank cells or text can produce formula errors.
Use SUMPRODUCT and MOD Functions to Count Matches
Combine the SUMPRODUCT and MOD functions to evaluate multiple criteria across arrays without needing legacy array-entry shortcuts.
The SUMPRODUCT function natively handles arrays, making it ideal for checking multiple criteria row by row. By combining it with the MOD function (using a divisor of 10), you can isolate and evaluate the last digit of any whole number.
Click on the cell where you want the final matched count to be displayed.
Type the formula: =SUMPRODUCT((Sheet1!$D$2:$D$1000="Acme")*(MOD(Sheet1!$C$2:$C$1000,10)=B3)).
Replace 'Sheet1!$D$2:$D$1000' with your actual category column range, and change 'Acme' to the specific text you are searching for.
Replace 'Sheet1!$C$2:$C$1000' with your numerical data range, and 'B3' with the cell reference that contains the target last digit.
Press Enter to calculate. The formula will multiply the TRUE/FALSE arrays and return the total count of matching rows.
Easily Manage Complex Formulas with WPS Spreadsheet
WPS Spreadsheet natively supports advanced array formulas, including SUMPRODUCT and MOD. You can perform complex conditional counts and analyze massive datasets within a familiar, highly efficient interface.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open Spreadsheet, and load your data document.
- 2. Enter your conditional formula: Click your target cell and input the combined =SUMPRODUCT(...) formula as described above.
- 3. Calculate and analyze: Press Enter. WPS Spreadsheet processes the array calculations instantly, giving you an accurate count of matching rows.
Frequently Asked Questions
Why does my SUMPRODUCT formula return a #VALUE! error?
This error typically occurs if your specified ranges are not perfectly equal in size (for example, D2:D1000 and C2:C999). It can also happen if there is text in the column where the MOD function expects numerical values.
Can I use COUNTIFS instead of SUMPRODUCT for this?
No. The COUNTIFS function requires standard range evaluations and does not support embedding other mathematical operations (like MOD) directly within its criteria arguments. SUMPRODUCT is required to manipulate the array before counting.
How can I count based on the first digit instead of the last digit?
To evaluate the first digit, substitute the MOD function with the LEFT function. Use VALUE(LEFT(Range, 1)) within your SUMPRODUCT formula to extract the starting number and evaluate it mathematically against your target.
What does the asterisk (*) do in the SUMPRODUCT formula?
The asterisk acts as the AND operator between arrays. It multiplies the logical TRUE (1) and FALSE (0) results generated by your criteria. Only rows where both conditions are TRUE will yield a 1, which SUMPRODUCT then totals.
