Math
COUNTIF & SUMIF Across Non-Contiguous Ranges (Additive & VSTACK)
Count matching rows and sum quantities from two separate blocks, with literal criteria and a dynamic-array alternative.
Combine conditional summaries from separate blocks
Add one COUNTIF or SUMIF per block, or stack the aligned labels and quantities with VSTACK before filtering. A multi-area union is not a portable single-range input to these conditional functions.
The search value must be a nonempty text label. SUMIF and COUNTIF criteria interpret wildcard and comparison syntax, so prefix the escaped label with = and escape ~, * and ?. The VSTACK equality comparison treats the label literally. Both approaches use case-insensitive comparisons for these text labels.
All six quantities must be numeric for the sum examples. Zero and negative adjustments are valid. Counts measure matching rows, not distinct part types: repeated labels in either block each count again. Keep the label and quantity ranges aligned and avoid overlapping blocks unless double counting is intentional.
| Warehouse Block | Part & Qty Rows | Matches for G2 = "Bolt" |
|---|---|---|
| East Bin (A2:B4) | Bolt: 50, Nut: 30, Bolt: 70 | Count = 2 | Sum = 120 |
| West Bin (D2:E4) | Washer: 40, Bolt: 90, Nut: 20 | Count = 1 | Sum = 90 |
| Combined Total | Additive SUMIF or VSTACK + FILTER | Count = 3 | Total Qty = 210 |
Add separate SUMIF totals
Each block is summarized separately using the same escaped literal criterion.
=IF(OR(NOT(ISTEXT(G2)),COUNTBLANK(G2)>0),"Enter part label",IF(COUNT(B2:B4,E2:E4)<>6,"Invalid quantities",SUMIF(A2:A4,"="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G2,"~","~~"),"*","~*"),"?","~?"),B2:B4)+SUMIF(D2:D4,"="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G2,"~","~~"),"*","~*"),"?","~?"),E2:E4)))Dynamic Array VSTACK + FILTER Equivalent
The aligned VSTACK arrays have six rows each. FILTER returns a zero placeholder when no rows match, so SUM returns zero. This version requires dynamic-array function support.
=IF(OR(NOT(ISTEXT(G2)),COUNTBLANK(G2)>0),"Enter part label",IF(COUNT(B2:B4,E2:E4)<>6,"Invalid quantities",SUM(FILTER(VSTACK(B2:B4,E2:E4),VSTACK(A2:A4,D2:D4)=G2,0))))Count matching rows across both blocks
Quantity validity does not affect this count of labels. Repeated part labels remain separate matching rows.
=IF(OR(NOT(ISTEXT(G2)),COUNTBLANK(G2)>0),"Enter part label",COUNTIF(A2:A4,"="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G2,"~","~~"),"*","~*"),"?","~?"))+COUNTIF(D2:D4,"="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G2,"~","~~"),"*","~*"),"?","~?")))Try the example
Use the original values or change them to test your understanding.