Hàm SUMPRODUCT Trong Excel: Tính Toán Có Điều Kiện Không Cần Cột Phụ

1. SUMPRODUCT là gì? #

SUMPRODUCT nhân từng phần tử của các mảng tương ứng rồi cộng tổng. Nghe đơn giản, nhưng khi kết hợp với điều kiện logic, nó trở thành công cụ tính toán có điều kiện MẠNH NHẤT Excel — không cần cột phụ, không cần Ctrl+Shift+Enter.

=SUMPRODUCT(array1, [array2], [array3], ...)

2. Cách hoạt động cơ bản #

2.1. Ví dụ: Tổng doanh thu = Số lượng × Đơn giá #

=SUMPRODUCT(B2:B4, C2:C4)

Tính: (10×500000) + (25×200000) + (5×800000) = 14,000,000

3. SUMPRODUCT với điều kiện #

Đây mới là sức mạnh thật sự. Dùng biểu thức logic (điều_kiện) tạo mảng 1/0:

3.1. Ví dụ: Tổng doanh số phòng Kinh doanh #

=SUMPRODUCT((A2:A100="Kinh doanh")*C2:C100)

Cách hoạt động:

• (A2:A100="Kinh doanh") → mảng TRUE/FALSE → nhân sẽ thành 1/0

• Nhân với C2:C100 → chỉ giữ lại giá trị ở hàng "Kinh doanh"

• SUMPRODUCT cộng tổng

3.2. Nhiều điều kiện (AND) #

Tổng doanh số phòng KD, nhân viên nữ:

=SUMPRODUCT((A2:A100="Kinh doanh")*(B2:B100="Nữ")*C2:C100)

3.3. Nhiều điều kiện (OR) #

Tổng doanh số phòng KD HOẶC Marketing:

=SUMPRODUCT(((A2:A100="Kinh doanh")+(A2:A100="Marketing"))*C2:C100)

Dấu + = OR, nhưng cần bọc trong thêm 1 lần nhân (vì OR có thể tạo giá trị > 1):

=SUMPRODUCT(((A2:A100="Kinh doanh")+(A2:A100="Marketing")>0)*C2:C100)

4. SUMPRODUCT thay COUNTIFS #

Đếm nhân viên phòng KD, nữ, lương > 10 triệu:

=SUMPRODUCT((A2:A100="Kinh doanh")*(B2:B100="Nữ")*(C2:C100>10000000)*1)

Nhân *1 ở cuối để ép mảng TRUE/FALSE thành 1/0 rồi cộng = đếm.

5. SUMPRODUCT thay AVERAGEIFS #

Trung bình lương phòng KD:

=SUMPRODUCT((A2:A100="Kinh doanh")*C2:C100) / SUMPRODUCT((A2:A100="Kinh doanh")*1)

Tử = tổng lương KD, mẫu = số người KD.

6. SUMPRODUCT với so sánh text (Wildcard) #

SUMPRODUCT không hỗ trợ wildcard trực tiếp. Dùng các hàm phụ:

6.1. Chứa text #

=SUMPRODUCT((ISNUMBER(SEARCH("iPhone", B2:B100)))*C2:C100)

SEARCH trả vị trí (nếu tìm thấy) hoặc lỗi → ISNUMBER chuyển thành TRUE/FALSE.

6.2. Bắt đầu bằng #

=SUMPRODUCT((LEFT(B2:B100, 3)="ABC")*C2:C100)

7. SUMPRODUCT thay Pivot Table #

Tạo bảng tổng hợp cross-tab không cần Pivot:

Header hàng: Phòng ban (E2:E5). Header cột: Giới tính (F1:G1).

=SUMPRODUCT(($A$2:$A$100=$E2)*($B$2:$B$100=F$1)*$C$2:$C$100)

Lock $E2 (hàng) và F$1 (cột) → copy công thức ra cả bảng.

8. Ứng dụng nâng cao #

8.1. Đếm unique values #

=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))

Mỗi giá trị xuất hiện N lần → 1/N → tổng = số giá trị unique.

8.2. Tổng N giá trị lớn nhất #

=SUMPRODUCT(LARGE(C2:C100, ROW(INDIRECT("1:5"))))

Tổng 5 giá trị lớn nhất trong vùng C2:C100.

8.3. So sánh 2 danh sách #

Đếm phần tử chung giữa 2 danh sách:

=SUMPRODUCT(COUNTIF(A2:A100, B2:B50))

9. SUMPRODUCT vs SUMIFS #

Quy tắc: SUMIFS cho điều kiện đơn giản (nhanh), SUMPRODUCT cho logic phức tạp (linh hoạt).

10. Mẹo SUMPRODUCT #

1. Dấu `--` thay `*1`: -- (double negative) chuyển TRUE/FALSE → 1/0 nhanh gọn

2. Tránh toàn cột: SUMPRODUCT(A:A*B:B) → chậm. Dùng vùng cụ thể

3. Debug: Chọn phần mảng → F9 → xem mảng kết quả trung gian

4. Kết hợp IFERROR: Bọc IFERROR cho hàm con (SEARCH, FIND) để tránh lỗi

5. Dùng N(): N(điều_kiện) rõ ràng hơn (điều_kiện)*1

11. Tổng kết #

SUMPRODUCT là "Swiss Army knife" của Excel — một hàm để xử lý tổng có điều kiện, đếm, trung bình, so sánh mảng, và nhiều tác vụ mà bình thường cần cột phụ hoặc Pivot Table. Nắm vững SUMPRODUCT giúp công thức bạn gọn, mạnh, và chuyên nghiệp.