SUMPRODUCT Multiple Criteria AND/OR + Partial Match (Wildcard) Patterns
For complex aggregation, knowing the SUMPRODUCT multiple-criteria OR pattern lets you handle everything in a single cell without helper columns. This article organizes eight ready-to-use AND/OR/NOT and partial-match patterns. Prerequisites: SUMPRODUCT Guide, SUMIFS Guide.
Quick Fix — The 8 Most Common Patterns
=SUMPRODUCT(--(A2:A100="영업"), --(B2:B100>=DATE(2025,9,1)), --(B2:B100<DATE(2025,10,1)), C2:C100)=SUMPRODUCT(--((A2:A100="영업")+(A2:A100="개발")>0), C2:C100)=SUMPRODUCT(--ISNUMBER(MATCH(A2:A100, {"영업","개발","물류"}, 0)), C2:C100)=SUMPRODUCT(--(A2:A100<>"외주"), C2:C100)=SUMPRODUCT(--((A2:A100="영업")+(A2:A100="개발")>0), --(B2:B100>=DATE(2025,9,1)), --(B2:B100<DATE(2025,10,1)), C2:C100)=SUMPRODUCT(--ISNUMBER(SEARCH("긴급", D2:D100)), C2:C100)=SUMPRODUCT(--(LEFT(D2:D100,3)="ABC"), C2:C100)=SUMPRODUCT(--( ISNUMBER(SEARCH("긴급",D2:D100)) + ISNUMBER(SEARCH("지연",D2:D100)) + ISNUMBER(SEARCH("반품",D2:D100)) > 0 ), C2:C100)How It Works: 1/0 Arrays × Target Column = Filter
Convert TRUE/FALSE conditions to 1/0 with --, multiply them together to apply them like filters, then multiply by the target column and sum the results.
“– (Double Unary)” and +(condition)>0
--(condition) converts values to 1/0. Connect AND conditions with multiplication, and use (condition1)+(condition2)>0 for OR conditions.
AND Patterns
=SUMPRODUCT(--(A2:A100="영업"), --(E2:E100="온라인"), --(F2:F100>=100), C2:C100)OR Patterns
=SUMPRODUCT(--((A2:A100="영업")+(A2:A100="개발")>0), C2:C100)=SUMPRODUCT(--ISNUMBER(MATCH(A2:A100, H2:H4, 0)), C2:C100)NOT (Exclusion) and Mixed Conditions
=SUMPRODUCT(--(A2:A100<>"외주"), C2:C100)=SUMPRODUCT(--((A2:A100="영업")+(A2:A100="개발")>0), --ISNA(SEARCH("긴급",D2:D100)), C2:C100)Partial Matches/Wildcards
=SUMPRODUCT(--ISNUMBER(SEARCH("긴급", D2:D100)), C2:C100)=SUMPRODUCT(--(LEFT(D2:D100,2)="대기"), --ISNA(SEARCH("긴급", D2:D100)), C2:C100)Safe Date-Range Pattern
=SUMPRODUCT(--(B2:B100>=DATE(2025,9,1)), --(B2:B100<DATE(2025,10,1)), C2:C100)Practical Example Table + Expected Results
| Row | Department | Date | Amount | Notes |
|---|---|---|---|---|
| 2 | Sales | 2025-09-05 | 120000 | Urgent delivery |
| 3 | Development | 2025-09-10 | 68000 | Normal |
| 4 | Logistics | 2025-09-12 | 45000 | Delay occurred |
| 5 | Sales | 2025-09-28 | 88000 | Return processed |
| 6 | Development | 2025-10-02 | 134000 | Urgent |
OR + monthly range example: 276000 / Contains “urgent” example: 254000
Performance Optimization Checklist
- Keep ranges as small as possible (avoid entire-column references) and use Excel Tables.
- Ensure all arguments have the same number of rows and columns.
- Apply text operations only to the columns you need.
- For large datasets, use PivotTables or Power Pivot.
Troubleshooting
| Symptom | Cause | Solution |
|---|---|---|
| #VALUE! | Mismatched range sizes | Make all ranges the same size. |
| Result is 0 | Typo or formatting issue | Check quotation marks, spaces, and date values. |
| Slow | Entire-column references or excessive SEARCH use | Reduce ranges and consider helper columns. |
| Sum is too high | Text numbers and blank cells are mixed in | Convert with -- and exclude blank cells. |
Wrap-Up & Recommended Next Articles
These eight patterns can solve most aggregation tasks. Learn more: SUMPRODUCT Basics, SUMIFS, COUNTIFS, XLOOKUP, IF Function