Nội dung bài viết
Nhân thủ công từng ô số lượng với đơn giá trong Excel rồi cộng tổng khiến bạn mất nhiều thời gian, cồng kềnh bảng tính và rất dễ xảy ra sai sót. Hàm SUMPRODUCT trong Excel chính là giải pháp tối ưu giúp bạn thực hiện đồng thời phép nhân và cộng dồn dữ liệu theo nhiều điều kiện linh hoạt chỉ trong một công thức duy nhất. Hãy cùng MSO khám phá ngay công thức, cơ chế vận hành và các ví dụ ứng dụng từ cơ bản đến nâng cao để nâng cao hiệu suất xử lý báo cáo ngay dưới đây!
Hàm SUMPRODUCT trong Excel là gì? Công thức và cách hoạt động
Đây là hàm tính toán nâng cao cho phép lấy tích của các phần tử tương ứng trong nhiều mảng dữ liệu rồi cộng tổng các tích đó lại với nhau cực kỳ hiệu quả mà không cần tạo cột phụ.
Định nghĩa hàm SUMPRODUCT trong Excel
Tên gọi SUMPRODUCT là sự kết hợp của hai thuật ngữ toán học căn bản trong tiếng Anh:
- SUM: Phép tính tổng.
- PRODUCT: Phép tính tích (kết quả của phép nhân).
Về cơ chế xử lý, hàm SUMPRODUCT vận hành theo 2 bước tự động liên tiếp:
- Thực hiện phép nhân các giá trị ở vị trí tương ứng giữa các dãy số (mảng dữ liệu).
- Cộng toàn bộ các kết quả tích vừa tính được để trả về một giá trị tổng duy nhất.
Công thức cơ bản của hàm SUMPRODUCT
Cấu trúc công thức chuẩn của hàm SUMPRODUCT được thiết lập như sau:
=SUMPRODUCT(array1, [array2], [array3], …)
Chi tiết các tham số:
- array1 (Bắt buộc): Mảng hoặc vùng chọn dữ liệu đầu tiên chứa các giá trị cần tính toán.
- array2, array3,… (Tùy chọn): Các mảng dữ liệu tiếp theo muốn nhân kết hợp (hỗ trợ tối đa 255 mảng).
*Lưu ý: Các mảng tham gia vào công thức (array1, array2,…) bắt buộc phải đồng nhất về kích thước (cùng số hàng và cùng số cột). Nếu các vùng chọn chênh lệch kích thước, Excel sẽ trả về lỗi #VALUE!.
Cơ chế và nguyên lý hoạt động
Để dễ hình dung, giả sử bạn truyền vào hai vùng dữ liệu A2:A4 và B2:B4. Khi áp dụng công thức =SUMPRODUCT(A2:A4, B2:B4), hệ thống sẽ tự động tính toán theo biểu thức toán học:
Tổng=(A2B2)+(A3B3)+(A4B4)
Quy tắc xử lý dữ liệu:
Khi các mảng được phân tách bằng dấu phẩy (,), nếu vùng chọn chứa ô trống, ô dạng văn bản (Text) hoặc giá trị logic (TRUE/FALSE), hàm SUMPRODUCT sẽ tự động quy đổi các giá trị này về 0 thay vì báo lỗi.
Hướng dẫn cách dùng hàm SUMPRODUCT trong Excel từ cơ bản đến nâng cao
Các ví dụ chi tiết dưới đây sẽ hướng dẫn bạn ứng dụng hàm SUMPRODUCT trong Excel vào các bài toán thực tế từ tính doanh thu, tính điểm trung bình trọng số đến đếm dữ liệu theo nhiều điều kiện cực nhanh.
Tính tổng tích cơ bản (Ví dụ tính tổng doanh thu)
Giả sử bạn có bảng dữ liệu bán hàng với các thông số sau:
- Vùng B2:B6: Số lượng mặt hàng bán ra.
- Vùng C2:C6: Đơn giá tương ứng của từng mặt hàng.
Mục tiêu: Tính tổng doanh thu toàn bộ đơn hàng mà không cần tạo cột “Thành tiền” trung gian.
Công thức thực hiện:
=SUMPRODUCT(B2:B6, C2:C6)
Cách Excel xử lý chi tiết:
Hệ thống tự động thực hiện phép nhân từng ô tương ứng giữa 2 cột: (B2 * C2) + (B3 * C3) + (B4 * C4) + (B5 * C5) + (B6 * C6) rồi cộng dồn lại để trả về ngay số tổng chính xác.
Tính tổng có 1 điều kiện (Thay thế hàm SUMIF)
Giả sử bảng dữ liệu mở rộng thêm Cột A2:A6 chứa tên danh mục sản phẩm (“Điện thoại”, “Laptop”, “Máy tính bảng”).
Mục tiêu: Tính tổng doanh thu thu được từ riêng mặt hàng “Điện thoại”.
Công thức thực hiện:
=SUMPRODUCT((A2:A6=”Điện thoại”) * B2:B6 * C2:C6)
Giải thích cơ chế chuyển đổi logic:
- Biểu thức (A2:A6=”Điện thoại”) tạo ra mảng giá trị Đúng/Sai: {TRUE; FALSE; TRUE; …}.
- Khi gặp toán tử nhân (*), Excel tự động ép kiểu dữ liệu logic: TRUE biến thành 1, FALSE biến thành 0.
- Các dòng không thỏa mãn điều kiện sẽ bị nhân với 0 (cho kết quả bằng 0). Chỉ những dòng chứa chữ “Điện thoại” mới giữ lại giá trị tích để cộng dồn vào tổng cuối cùng.
Tính tổng có nhiều điều kiện phức tạp (Thay thế hàm SUMIFS)
Mục tiêu: Tính tổng doanh thu của sản phẩm “Laptop” bán tại chi nhánh “Hà Nội” (Cột D) phát sinh trong “Tháng 9” (Cột E chứa dữ liệu ngày tháng).
Công thức thực hiện:
=SUMPRODUCT((A2:A10=”Laptop”) * (D2:D10=”Hà Nội”) * (MONTH(E2:E10)=9) * B2:B10 * C2:C10)
Giải thích chi tiết:
Hàm kiểm tra đồng thời 3 điều kiện: Tên sản phẩm, Chi nhánh và Tháng (sử dụng hàm MONTH để trích xuất tháng từ Cột E). Chỉ khi tất cả các điều kiện đều đúng (111=1), giá trị tích Số lượng * Đơn giá mới được tính vào kết quả.
Tính điểm trung bình trọng số (Weighted Average)
Tính điểm trung bình học tập theo số tín chỉ hoặc tính giá bán trung bình theo khối lượng hàng hóa là các bài toán rất phổ biến:
- Vùng B2:B6: Điểm số từng môn học.
- Vùng C2:C6: Số tín chỉ tương ứng của các môn.
Mục tiêu: Tính điểm trung bình trọng số toàn khóa cho sinh viên.
Công thức thực hiện:
=SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6)
Giải thích chi tiết:
- Tử số: =SUMPRODUCT(B2:B6, C2:C6) tính tổng điểm tích lũy bằng cách nhân từng điểm môn với số tín chỉ rồi cộng lại.
- Mẫu số: =SUM(C2:C6) tính tổng số tín chỉ đăng ký.
- Phép chia cho ra kết quả điểm trung bình trọng số hoàn toàn chuẩn xác.
Đếm số lượng dữ liệu theo nhiều điều kiện (Thay thế hàm COUNTIFS)
Bạn có thể sử dụng hàm SUMPRODUCT để đếm số lượng bản ghi thỏa mãn đồng thời nhiều tiêu chí bằng toán tử trừ kép (—).
Mục tiêu: Đếm số đơn hàng có trạng thái “Thành công” (Cột A) thuộc khu vực “Miền Bắc” (Cột B).
Công thức thực hiện:
=SUMPRODUCT(–(A2:A10=”Thành công”), –(B2:B10=”Miền Bắc”))
Giải thích vai trò toán tử trừ kép (—):
- Biểu thức điều kiện tạo mảng dạng {TRUE; FALSE; TRUE; …}.
- Dấu trừ thứ nhất (–) chuyển TRUE/FALSE thành -1/0.
- Dấu trừ thứ hai (—) đổi dấu thành 1/0.
- Hàm SUMPRODUCT thực hiện cộng toàn bộ các số 1 lại với nhau, trả về chính xác số lượng bản ghi thỏa mãn điều kiện.
Lý do nên sử dụng hàm SUMPRODUCT trong Excel
Sử dụng hàm SUMPRODUCT trong Excel giúp rút gọn quy trình xử lý dữ liệu, loại bỏ cột phụ cồng kềnh và thay thế nhiều công thức mảng phức tạp chỉ mất vài phút.
Loại bỏ các cột phụ cồng kềnh
Khi tính toán tổng giá trị đơn hàng theo cách thủ công truyền thống, bạn thường phải chèn thêm một cột phụ “Thành tiền” với công thức =Số lượng * Đơn giá cho từng hàng, rồi mới tính tổng bằng hàm SUM ở cuối bảng.
Khi ứng dụng hàm SUMPRODUCT, công thức sẽ thực hiện toàn bộ các phép nhân trung gian ngầm bên trong bộ nhớ và trả về ngay kết quả tổng cuối cùng. Điều này giúp tối ưu không gian hiển thị, loại bỏ các cột không cần thiết và giữ cho giao diện trang tính luôn gọn gàng, chuyên nghiệp.
Xử lý dữ liệu mảng không cần nhấn Ctrl + Shift + Enter
Trước đây, để xử lý các phép tính nâng cao theo dạng mảng dữ liệu, người dùng bắt buộc phải thiết lập công thức mảng (Array Formula) phức tạp và nhấn tổ hợp phím Ctrl + Shift + Enter.
Hàm SUMPRODUCT vốn được thiết kế với cơ chế tính toán mảng mặc định. Nhờ đó, bạn có thể thực hiện phép nhân và cộng dồn dữ liệu theo nhiều điều kiện linh hoạt mà chỉ cần nhấn phím Enter thông thường, giúp thao tác tiện lợi hơn và hạn chế lỗi công thức.
Thay thế và khắc phục hạn chế của SUMIFS, COUNTIFS
Mặc dù SUMIFS hay COUNTIFS là các hàm điều kiện rất phổ biến, chúng vẫn tồn tại những rào cản nhất định: không hỗ trợ tính toán trực tiếp trên các vùng dữ liệu chứa công thức biến đổi (như trích xuất ngày/tháng/năm hay độ dài chuỗi ký tự) và khó kết hợp với các toán tử điều kiện phức tạp.
Hàm SUMPRODUCT cho phép bạn chèn trực tiếp các hàm xử lý chuỗi (LEN, SEARCH), hàm thời gian (MONTH, YEAR) hay các phép toán logic vào ngay trong vùng điều kiện. Nhờ đó, hàm giúp giải quyết triệt để các bài toán tổng hợp dữ liệu nâng cao mà SUMIFS hay COUNTIFS không xử lý được.
Các hàm phối hợp phổ biến với hàm SUMPRODUCT trong Excel
Việc kết hợp hàm SUMPRODUCT trong Excel với các hàm xử lý văn bản, thời gian hay tra cứu giúp mở rộng tối đa khả năng tính toán trên các tập dữ liệu báo cáo phức tạp.
Kết hợp với hàm xử lý chuỗi (LEN, ISNUMBER, SEARCH)
Khi cần đếm hoặc tính toán trên vùng dữ liệu văn bản chứa ký tự/từ khóa bất kỳ (tìm kiếm tương đối):
Mục tiêu: Đếm số dòng trong vùng A2:A10 có chứa từ “Khuyến mãi”.
Công thức thực hiện:
=SUMPRODUCT(–ISNUMBER(SEARCH(“Khuyến mãi”, A2:A10)))
Giải thích: Hàm SEARCH tìm vị trí từ khóa; ISNUMBER trả về TRUE nếu tìm thấy; toán tử — chuyển thành 1 để SUMPRODUCT tính tổng số lượng ô thỏa mãn.
Kết hợp với hàm thời gian (MONTH, YEAR, WEEKDAY)
Tự do tổng hợp số liệu theo thời gian thực tế mà không cần chèn thêm cột phụ trích xuất tháng hay năm:
Mục tiêu: Tính tổng số lượng hàng bán ra (Cột B) phát sinh trong năm 2026 (Cột A chứa dữ liệu ngày tháng).
Công thức thực hiện:
=SUMPRODUCT((YEAR(A2:A10)=2026) * B2:B10)
Giải thích: Hàm YEAR trực tiếp trích xuất năm từ vùng A2:A10, lọc các dòng thỏa mãn năm 2026 để nhân với số lượng tương ứng ở Cột B.
Kết hợp với hàm tra cứu (INDEX, MATCH)
Khi cần tra cứu và tính toán trên bảng dữ liệu đa chiều (dạng ma trận 2 chiều gồm cả hàng và cột), hàm SUMPRODUCT có thể kết hợp linh hoạt với INDEX và MATCH để trích xuất và tính tổng chính xác tại các điểm giao nhau giữa các điều kiện hàng/cột.
Các lỗi thường gặp khi sử dụng hàm SUMPRODUCT trong Excel và cách khắc phục
Hiểu rõ nguyên nhân gây lỗi khi dùng hàm SUMPRODUCT trong Excel sẽ giúp bạn xử lý sự cố công thức cực nhanh và đảm bảo độ chính xác cho báo cáo.
Lỗi #VALUE! do kích thước mảng không đồng nhất
- Nguyên nhân: Lỗi này xuất hiện ngay lập tức khi các vùng dữ liệu được chọn trong công thức có độ dài hoặc độ rộng không tương đồng (khác số lượng hàng hoặc số lượng cột).
- Ví dụ gây lỗi: =SUMPRODUCT(A2:A10, B2:B12) (Vùng A2:A10 chứa 9 hàng, trong khi vùng B2:B12 lại chứa 11 hàng).
- Cách khắc phục: Kiểm tra lại địa chỉ các vùng chọn trong công thức và điều chỉnh sao cho tất cả các mảng đều có kích thước hoàn toàn bằng nhau (Sửa lại thành A2:A10 và B2:B10).
Lỗi #VALUE! khi vùng dữ liệu chứa ô văn bản (Text format)
- Nguyên nhân: Khi bạn sử dụng toán tử nhân (*) để kết nối các mảng dữ liệu, nếu một trong các ô thuộc vùng chọn có chứa ký tự chữ (ví dụ: hàng tiêu đề hoặc ghi chú bằng văn bản), phép nhân toán học sẽ không thể thực hiện và trả về lỗi #VALUE!.
- Cách khắc phục: Thay vì dùng toán tử nhân (*), bạn hãy chuyển sang dùng dấu phẩy (,) để phân tách giữa các mảng dữ liệu. Khi dùng dấu phẩy, hàm SUMPRODUCT sẽ tự động bỏ qua các ô chứa ký tự chữ và mặc định quy đổi giá trị của chúng về 0.
Kết quả trả về bằng 0 không như mong muốn
Nguyên nhân: Sự cố này thường đến từ việc dữ liệu đầu vào bị sai lệch về kiểu định dạng:
- Các ô chứa số liệu đang bị lưu dưới dạng văn bản (Text).
- Chuỗi ký tự điều kiện chứa các khoảng trắng dư thừa ở đầu hoặc cuối ô (ví dụ: “Laptop “ khác với “Laptop”).
Cách khắc phục:
- Sử dụng hàm TRIM hoặc công thức lồng ghép để xóa bỏ toàn bộ khoảng trắng thừa trong vùng dữ liệu.
- Chuyển đổi định dạng cột chứa số liệu từ dạng Text về lại dạng Number chuẩn (sử dụng tính năng Text to Columns hoặc công thức =VALUE()).
Nếu doanh nghiệp của bạn đang tìm kiếm một giải pháp hỗ trợ tối ưu hóa hiệu suất làm việc toàn diện, hãy tham khảo ngay bộ phần mềm Microsoft 365 bản quyền chính hãng tại MSO để trải nghiệm trọn bộ công cụ nâng cao cùng dịch vụ hỗ trợ kỹ thuật chuyên sâu cực nhanh!
Các câu hỏi thường gặp (FAQ)
Dưới đây là giải đáp chi tiết cho những câu hỏi thắc mắc phổ biến giúp bạn làm chủ hàm SUMPRODUCT trong Excel dễ dàng hơn:
Hàm SUMPRODUCT có thể thay thế hoàn toàn hàm SUMIFS và COUNTIFS không?
Có thể. Hàm SUMPRODUCT có thể đảm nhận hầu hết mọi bài toán của SUMIFS và COUNTIFS. Tuy nhiên, với các bảng dữ liệu quá đơn giản, sử dụng SUMIFS sẽ giúp công thức nhìn ngắn gọn hơn.
Nên dùng dấu phẩy (,) hay dấu nhân (*) phân tách các mảng trong hàm SUMPRODUCT?
- Dùng dấu phẩy (,) khi bạn chỉ cần tính tổng tích các vùng số thông thường (giúp bỏ qua ô dạng chữ).
- Dùng dấu nhân (*) khi bạn cần đưa các biểu thức điều kiện (TRUE/FALSE) vào trong công thức.
Sử dụng hàm SUMPRODUCT trên bảng dữ liệu quá lớn có làm file Excel bị chậm không?
Có thể ảnh hưởng nhẹ. Do hàm SUMPRODUCT phải tính toán từng ô theo cơ chế mảng, việc áp dụng công thức trên hàng trăm nghìn dòng dữ liệu phức tạp có thể làm file Excel xử lý chậm hơn một chút so với hàm SUMIFS thông thường.
Hàm SUMPRODUCT có phân biệt chữ hoa và chữ thường không?
Không. Mặc định hàm SUMPRODUCT không phân biệt chữ hoa hay chữ thường (ví dụ: “EXCEL” và “excel” được tính là giống nhau). Nếu muốn phân biệt chính xác chữ hoa/thường, bạn có thể kết hợp thêm hàm EXACT.
Tại sao công thức hàm SUMPRODUCT của tôi không chạy khi gửi cho đồng nghiệp?
Tình trạng này thường do sự khác biệt về cài đặt dấu phân cách hệ thống trên máy tính (Dấu phẩy , hoặc dấu chấm phẩy ;). Bạn chỉ cần kiểm tra và điều chỉnh lại dấu phân cách trong cài đặt Region của Windows là công thức sẽ hoạt động bình thường.
Lời kết
Thành thạo hàm SUMPRODUCT trong Excel giúp bạn xử lý các phép tính phức tạp nhanh chóng, tối ưu hóa cấu trúc bảng tính và nâng cao năng suất làm việc. Hàm SUMPRODUCT là một công cụ tính toán đa năng, hỗ trợ gộp nhiều bước xử lý số liệu thủ công thành một thao tác tự động đơn giản.
———————————————————
Fanpage: MSO.vn – Microsoft 365 Việt Nam
Hotline: 024.9999.7777
