=SUMPRODUCT(B2:B6,C2:C6) nhân từng số lượng ở cột B với đơn giá bên cạnh ở cột C, rồi cộng các kết quả lại. Hàm cho tổng tiền đơn hàng trong một ô, không cần cột thành tiền từng dòng.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Qty | Price | Line total | Total | |
| 2 | Pen | 4 | $1.50 | $6.00 | $30.70 | |
| 3 | Notebook | 2 | $3.25 | $6.50 | $30.70 | |
| 4 | Folder | 5 | $0.80 | $4.00 | ||
| 5 | Stapler | 1 | $7.90 | $7.90 | ||
| 6 | Marker | 3 | $2.10 | $6.30 |
F2 và F3 hiện cùng $30.70. Cột thành tiền D chỉ ở đó để cho thấy SUMPRODUCT làm gì: 4 × 1.50, 2 × 3.25, cứ thế, rồi một phép SUM. Đổi một số lượng và cả hai tổng đều thay đổi theo.
Cú pháp hàm SUMPRODUCT
=SUMPRODUCT(array1, [array2], [array3], ...)
- Mỗi mảng là một vùng hoặc một phép tính tạo ra mảng, và tất cả phải cùng kích thước, nếu không SUMPRODUCT trả về
#VALUE!. - Với hai mảng trở lên, các giá trị ở cùng vị trí được nhân với nhau, rồi các tích được cộng lại.
- Với một mảng, hàm chỉ cộng mảng đó lại, và đó là lý do các dạng có điều kiện bên dưới hoạt động:
=SUMPRODUCT((A2:A7="North")*C2:C7)chỉ có một mảng, đã được nhân sẵn. - Văn bản truyền vào như một đối số riêng được tính là 0. Văn bản nằm trong một phép tính
*gây ra#VALUE!.
SUMPRODUCT làm việc với mảng trên mọi phiên bản Excel mà không cần Ctrl+Shift+Enter (Cmd+Shift+Enter trên Mac), vì vậy nó từng là công cụ chuẩn để tính tổng có điều kiện trước khi có SUMIFS, và đến giờ vẫn vậy với những trường hợp SUMIFS không xử lý được.
SUMPRODUCT có điều kiện
Một phép so sánh trên vùng, A2:A7="North", trả về một TRUE hoặc FALSE cho mỗi dòng. Nhân với nó sẽ giữ các dòng TRUE (×1) và biến các dòng còn lại thành 0 (×0). Nhân hai phép so sánh với nhau để có AND.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Formula | Result | |
| 2 | North | Apple | 120 | North sales | 230 | |
| 3 | South | Pear | 45 | North Apple sales | 150 | |
| 4 | North | Pear | 80 | Count North | 3 | |
| 5 | East | Apple | 55 | Count over 50 | 4 | |
| 6 | South | Apple | 200 | Without -- | 0 | |
| 7 | North | Apple | 30 |
F2 cộng ba dòng North: 230. F3 nhân hai điều kiện, nên một dòng chỉ được tính khi cả hai đều đúng: 150. Để đếm thay vì cộng, bỏ các giá trị đi và biến TRUE/FALSE thành số bằng -- (hai dấu trừ): F4 đếm được 3 dòng North. F6 cho thấy vì sao -- quan trọng: SUMPRODUCT không cộng các giá trị TRUE, nên công thức thiếu nó trả về 0.
Bốn công thức đầu cho cùng kết quả với SUMIF, SUMIFS và COUNTIF. Phần tiếp theo mới là chỗ SUMPRODUCT thật sự có ích.
Những điều kiện SUMIFS không diễn đạt được
SUMIFS so một cột với một tiêu chí cố định. Nó không lấy được tháng của một ngày, không so được hai cột với nhau, và không nhân số lượng với đơn giá trước khi cộng. SUMPRODUCT làm được, vì mỗi điều kiện chỉ là một phép tính bình thường.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Date | Target | Actual | Formula | Result | |
| 2 | North | 2026-01-05 | 100 | 120 | February sales | 135 | |
| 3 | South | 2026-01-12 | 60 | 45 | Rows over target | 3 | |
| 4 | North | 2026-02-03 | 90 | 80 | North or East sales | 285 | |
| 5 | East | 2026-02-18 | 50 | 55 | Above target by | 75 | |
| 6 | South | 2026-03-02 | 150 | 200 | |||
| 7 | North | 2026-03-20 | 40 | 30 |
- G2 lấy MONTH của mọi ngày và giữ các dòng tháng 2: 80 + 55 = 135. Công thức này cộng tháng 2 của mọi năm; thêm
*(YEAR(B2:B7)=2026)nếu chỉ muốn một năm. - G3 so hai cột theo từng dòng và đếm các dòng mà Actual vượt Target.
- G4 là một phép OR: cộng hai điều kiện cho 1 khi một trong hai đúng (2 khi cả hai cùng đúng, đó là lý do có
>0). North hoặc East: 285. - G5 cộng phần mỗi dòng vượt mục tiêu, chỉ với những dòng đã vượt.
SUMPRODUCT cho tổng và trung bình có trọng số
Số lượng nhân đơn giá là một tổng có trọng số, và bạn có thể thêm điều kiện vào đó. Cùng ý tưởng ấy chia cho tổng các trọng số sẽ cho trung bình có trọng số: =SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6) là giá trung bình trên mỗi món đã bán.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Qty | Price | Formula | Result | |
| 2 | North | Apple | 10 | $1.20 | North revenue | $31.00 | |
| 3 | South | Pear | 4 | $1.50 | All revenue | $61.00 | |
| 4 | North | Pear | 6 | $1.50 | Average price per item | $1.36 | |
| 5 | East | Apple | 8 | $1.20 | |||
| 6 | South | Apple | 12 | $1.20 | |||
| 7 | North | Plum | 5 | $2.00 |
North bán 10 quả táo giá 1.50 và 5 quả mận giá 31.00. Trung bình cộng thông thường của các giá sẽ coi mận bán chạy ngang táo; G4 nhân mỗi giá với số lượng của nó làm trọng số.
Luyện tập: doanh thu có điều kiện
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Qty | Price | Formula | Result | |
| 2 | North | Apple | 10 | $1.20 | South revenue | ||
| 3 | South | Pear | 4 | $1.50 | |||
| 4 | North | Pear | 6 | $1.50 | |||
| 5 | East | Apple | 8 | $1.20 | |||
| 6 | South | Apple | 12 | $1.20 | |||
| 7 | North | Plum | 5 | $2.00 |
Đến lượt bạn: Tính doanh thu South: số lượng nhân đơn giá, chỉ với các dòng South. Viết công thức ở G2.
SUMPRODUCT và SUMIFS, và hai lỗi thường gặp
| Điều kiện | SUMIFS | SUMPRODUCT |
|---|---|---|
| Cột bằng một giá trị | =SUMIFS(C2:C7,A2:A7,"North") | =SUMPRODUCT((A2:A7="North")*C2:C7) |
| Có chứa văn bản | =SUMIFS(C2:C7,B2:B7,"*app*") | =SUMPRODUCT(ISNUMBER(SEARCH("app",B2:B7))*C2:C7) |
| Tháng của một ngày | không làm trực tiếp được | =SUMPRODUCT((MONTH(B2:B7)=2)*D2:D7) |
| Cột so với cột | không làm được | =SUMPRODUCT(--(D2:D7>C2:C7)) |
| Số lượng × đơn giá | không làm được | =SUMPRODUCT(C2:C7,D2:D7) |
Hãy ưu tiên SUMIFS khi nó làm được việc. Hàm này dễ đọc hơn, nhanh hơn trên hàng chục nghìn dòng, và nhận được cả cột. =SUMPRODUCT((A:A="North")*C:C) nhân trên hơn một triệu dòng và trả về #VALUE! ngay khi gặp tiêu đề văn bản ở C1, nên hãy đưa cho SUMPRODUCT các vùng chính xác như A2:A500.
Hai lỗi bạn sẽ gặp:
#VALUE!do các vùng khác kích thước.=SUMPRODUCT(B2:B6,C2:C7)bị lỗi. Mọi vùng phải bao cùng các dòng.#VALUE!do văn bản trong vùng được nhân. Một tiêu đề hay chữ "n/a" trongC2:C7làm hỏng(A2:A7="North")*C2:C7, vì không thể nhân văn bản. Hãy bắt đầu vùng ở dưới tiêu đề, hoặc truyền các giá trị thành đối số riêng:=SUMPRODUCT(--(A2:A7="North"),C2:C7)tính văn bản trong cột C là 0.
Câu hỏi thường gặp
Hàm SUMPRODUCT trong Excel dùng để làm gì?
Hàm nhân các vùng theo từng dòng rồi cộng các tích lại. =SUMPRODUCT(B2:B6,C2:C6) là B2C2 + B3C3 + ... + B6*C6, ví dụ số lượng nhân đơn giá rồi cộng thành tổng tiền đơn hàng.
Làm sao để dùng SUMPRODUCT có điều kiện?
Nhân với một phép so sánh: =SUMPRODUCT((A2:A7="North")*C2:C7) cộng C2:C7 cho các dòng North. Phép so sánh cho TRUE hoặc FALSE, khi nhân sẽ thành 1 hoặc 0.
Dấu -- trong SUMPRODUCT nghĩa là gì?
Đó là hai dấu trừ, biến TRUE và FALSE thành 1 và 0. =SUMPRODUCT(--(C2:C7>50)) đếm các giá trị lớn hơn 50. Không có nó, SUMPRODUCT coi TRUE/FALSE là 0 và trả về 0.
Nên dùng SUMPRODUCT hay SUMIFS?
Dùng SUMIFS khi tiêu chí của nó diễn đạt được điều kiện: dễ đọc hơn và nhanh hơn trên vùng lớn. Dùng SUMPRODUCT khi điều kiện cần một phép tính, như tháng của một ngày, so cột này với cột khác, hay số lượng nhân đơn giá.
Vì sao SUMPRODUCT trả về #VALUE!?
Các vùng có kích thước khác nhau (B2:B6 với C2:C7), hoặc một vùng được nhân bằng * có chứa văn bản. Hãy cho mọi vùng cùng kích thước, và truyền các vùng có văn bản thành đối số riêng, khi đó văn bản được tính là 0.
