Correct COUNTIFS and SUMIFS Array Formulas in Excel or Google Sheets
Question details
Correct a COUNTIFS or SUMIFS array formula that must select the proper date-based data column dynamically before evaluating text criteria.
- Product
- Microsoft Excel or Google Sheets
- Device & OS
- not provided
- Scenario
- The user needs to count records matching a combined value in A3 and B3 and either of two text patterns, using the column whose header matches the value in O2.
- Observed behavior
- The formula does not return the expected result because the INDEX and MATCH expression is incorrectly structured or brackets are misplaced.
Confirm that the value in O2 exactly matches one header in All_Premium!AD1:AJ1 and that the corresponding data columns run from AD through AJ. Ensure that the criteria values in A3 and B3 can be concatenated to match entries in All_Premium!AP:AP.
Use the corrected COUNTIFS formula with INDEX and MATCH
Use this formula when you need to dynamically select a date-based column by its header and count rows containing either cnp or cnc.
The INDEX function must return the selected column from All_Premium!AD:AJ before COUNTIFS applies the wildcard text criteria. MATCH locates the column position using the header named in O2.
Click the cell where you want the total count to appear, then enter the formula in the formula bar.
Paste =ARRAYFORMULA(SUM(COUNTIFS(All_Premium!AP:AP,A3&B3,INDEX(All_Premium!AD:AJ,0,MATCH($O$2,All_Premium!AD1:AJ1,0)),{"*cnp*","*cnc*"}))) into the selected cell.
Replace All_Premium with your sheet name if needed, verify that AD:AJ contains the selectable columns, and update the cnp and cnc criteria text to match your data.
Check header matches and aligned criteria ranges
Use this check when the corrected formula returns zero, an error, or a result that appears too low or too high.
Build Dynamic COUNTIFS Formulas in WPS Spreadsheets
WPS Spreadsheets lets you combine COUNTIFS, SUM, INDEX, and MATCH to count data from a column selected by a header value. Enter the formula in a result cell, verify the lookup range, and update criteria directly in the familiar spreadsheet formula bar.
- 1. Open the worksheet in WPS Spreadsheets: Open the .xlsx or .xls file, then select the sheet that contains the COUNTIFS calculation.
- 2. Verify the lookup header: Check that the value in O2 appears in the header range AD1:AJ1 and that the headers do not contain extra spaces.
- 3. Enter the dynamic counting formula: Click the output cell and enter the COUNTIFS formula using INDEX(All_Premium!AD:AJ,0,MATCH($O$2,All_Premium!AD1:AJ1,0)) as the dynamic criteria range.
- 4. Review the result and refine criteria: Press Enter, then edit the wildcard criteria or sheet ranges in the formula bar if your source data uses different text or columns.
Frequently Asked Questions
Why does COUNTIFS need INDEX and MATCH in this formula?
MATCH finds the position of the header specified in O2 within All_Premium!AD1:AJ1, and INDEX returns the full data column at that position. COUNTIFS can then evaluate that selected column as a criteria range.
What does the array of *cnp* and *cnc* criteria do?
The criteria array {"*cnp*","*cnc*"} checks for either text pattern. SUM adds the two COUNTIFS results so the formula returns one total.
Why does MATCH return #N/A in this formula?
MATCH returns #N/A when the O2 value does not exactly match a header in All_Premium!AD1:AJ1. Check spelling, hidden spaces, date formatting, and whether the target header is inside the specified range.
Can I use fixed ranges instead of entire columns in COUNTIFS?
Yes. For better performance on large files, use matching fixed ranges, such as AP2:AP5000 and AD2:AJ5000. Keep the header lookup range separate, for example AD1:AJ1.
