Dynamic Arrays
Sum an entire 2D matrix across columns matching a header tag (SUM & FILTER)
Total a multi-row, multi-column 2D table (B3:E5) across ONLY the columns whose Row-2 status tag equals "Active"—solving the classic 2D SUMIF limitation cleanly!
Filter columns first, then aggregate all rows
The status mask B2:E2 has one entry per column of B3:E5. FILTER selects matching columns and SUM totals all three rows of those columns. Do not substitute a one-row SUMIF criteria range for this two-dimensional operation.
This exercise requires all twelve amount cells numeric and every status plus target present. Zero and negative adjustments are valid; missing/text amounts report Invalid inputs. The ranges are fixed and do not include new rows or columns added outside them.
No matching columns produces a sum of zero. Their average is undefined, so the average example returns No matching columns instead of a misleading zero. Equality compares literal labels; * is not a wildcard criterion here.
| Product Row | Store-1 ("Active") | Store-2 ("Closed") | Store-3 ("Active") | Store-4 ("Active") |
|---|---|---|---|---|
| Row 2 Status Tag | Active | Closed | Active | Active |
| Line A (Row 3) | 10 | 20 (Excluded) | 30 | 40 |
| Line B (Row 4) | 15 | 25 (Excluded) | 35 | 45 |
| Line C (Row 5) | 20 | 30 (Excluded) | 25 | 50 (Active 2D Sum = 270!) |
Total all rows across columns matching header status G2
The explicit matching-column count handles the empty result without hiding arbitrary errors.
=IF(OR(COUNT(B3:E5)<>12,COUNTBLANK(B2:E2)>0,COUNTBLANK(G2)>0),"Invalid inputs",IF(SUMPRODUCT(--(B2:E2=G2))=0,0,SUM(FILTER(B3:E5,B2:E2=G2))))Average cell value across matching 2D columns
3 active columns x 3 product rows = 9 cells totaling 270 -> exact mean 30.
=IF(OR(COUNT(B3:E5)<>12,COUNTBLANK(B2:E2)>0,COUNTBLANK(G2)>0),"Invalid inputs",IF(SUMPRODUCT(--(B2:E2=G2))=0,"No matching columns",AVERAGE(FILTER(B3:E5,B2:E2=G2))))Try the example
Use the original values or change them to test your understanding.