Build a two-way summary: rows are products in G, columns are regions in H1:J1, amounts in C

公式

=SUMIFS($C:$C, $A:$A, $G2, $B:$B, H$1)

说明

Lock the data columns with $, lock the product key on the row ($G2), and lock the region key on the column (H$1). Fill across and down for a live matrix that updates without refreshing a pivot.

步骤

  1. Source: A = product, B = region, C = amount.
  2. Stub products in G2:G.
  3. Region headers in H1:J1.
  4. Fill the SUMIFS across the matrix.

变体

Add a date filter

E1/F1 are report start and end dates.

=SUMIFS($C:$C, $A:$A, $G2, $B:$B, H$1, $D:$D, ">="&$E$1, $D:$D, "<="&$F$1)

COUNTIFS matrix

Order counts instead of revenue.

=COUNTIFS($A:$A, $G2, $B:$B, H$1)

Average selling price

Revenue / quantity with a zero-safe wrap.

=IFERROR(SUMIFS($C:$C, $A:$A, $G2, $B:$B, H$1)/SUMIFS($E:$E, $A:$A, $G2, $B:$B, H$1), 0)