Conditional Aggregation
Count Item Occurrences Across Multiple Worksheets or Regional Logs
Total how many times a SKU, employee ID, or ticket code appears across separate regional tabs or log blocks without #REF! 3D-COUNTIF errors.
Why COUNTIF rejects 3D sheet ranges like North:West!A2:A50
While SUM(North:West!B2:B50) supports 3D sheet references, COUNTIF(North:West!A2:A50, G2) fails with #VALUE! because COUNTIF requires a single 2D range object.
Two clean patterns solve cross-sheet counting reliably: (1) summing explicit per-sheet COUNTIF terms (COUNTIF(North!A2:A50, G2) + COUNTIF(South!A2:A50, G2) + ...)—or placing one COUNTIF cell per sheet in a summary block and summing them—and (2) stacking ranges with VSTACK and comparing elementwise with SUMPRODUCT or FILTER.
| Search Key | North Log Hits | South Log Hits | West Log Hits | Workbook Total |
|---|---|---|---|---|
| SKU-204 | 2 | 1 | 2 | 5 |
| SKU-108 | 1 | 2 | 0 | 3 |
| SKU-999 (Absent) | 0 | 0 | 0 | 0 |
Additive Cross-Tab COUNTIF Consolidation
Evaluates COUNTIF independently on each regional range (or worksheet reference like North!A2:A5) and sums the integer counts—returning 0 cleanly when no tab matches.
=COUNTIF(A2:A5, E2) + COUNTIF(B2:B5, E2) + COUNTIF(C2:C5, E2)Count How Many Tabs Contain At Least One Match
Converting each regional COUNTIF > 0 test with IF(..., 1, 0) reveals breadth of presence (how many regions stocked the SKU) vs. total occurrences.
=IF(COUNTIF(A2:A5, E2) > 0, 1, 0) + IF(COUNTIF(B2:B5, E2) > 0, 1, 0) + IF(COUNTIF(C2:C5, E2) > 0, 1, 0)Try the example
Use the original values or change them to test your understanding.