Hướng dẫn cách sử dụng hàm SUMPRODUCT trong Google Sheets

Hàm SUMPRODUCT trong Google Sheets là một công cụ tính toán giúp xử lý dữ liệu mảng, áp dụng điều kiện linh hoạt. Hàm SUMPRODUCT có cách sử dụng khá đơn giản. Nếu bạn chưa biết cách thì đừng bỏ lỡ bài viết dưới đây mà GCS Việt Nam mang đến nhé. 

Hàm SUMPRODUCT trong Google Sheets là gì?

Hàm SUMPRODUCT là hàm tính toán được sử dụng linh hoạt trong Google Sheets. Hàm này cho phép người dùng thực hiện đồng thời hai thao tác cùng một lúc. Đó là nhân các mảng dữ liệu tương ứng với nhau, sau đó cộng tổng các kết quả đó lại. 

Hàm SUMPRODUCT còn có khả năng xử lý các biểu thức logic (TRUE/FALSE) một cách thông minh. Tức là khi bạn đặt một điều kiện vào bên trong công thức, Google Sheets sẽ coi TRUE là 1 và FALSE là 0. 

Ngoài ra, hàm SUMPRODUCT còn mang tới nhiều chức năng khác như là:

• Tính tổng tích từng phần tử tương ứng giữa các mảng.
• Thay thế các hàm đếm có điều kiện như COUNTIF, SUMIFF trong trường hợp nâng cao.
• Kết hợp điều kiện logic (AND, OR) bằng cách nhân hoặc cộng các mảng điều kiện.
• Dùng để tính trọng số trung bình, tổng có điều kiện, thống kế nâng cao,….

Cú pháp hàm SUMPRODUCT trong Google Sheets

Cú pháp khi sử dụng của hàm SUMPRODUCT khá đơn giản:

=SUMPRODUCT(mảng_1, [mảng_2], …)

Trong đó:

• mảng_1: Đây là đối số bắt buộc đầu tiên. Nó là một dải ô hoặc một mảng dữ liệu mà bạn muốn nhân.
• mảng_2: Đây là các đối số tùy chọn. Bạn có thể thêm nhiều dải ô hoặc mảng khác để nhân với nhau. Số lượng mảng có thể lên đến 30. 

*Lưu ý: Tất cả các mảng dữ liệu được sử dụng trong hàm SUMPRODUCT phải có cùng kích thước và hình dạng. Ví dụ, nếu mảng 1 là một cột gồm 10 ô, các mảng tiếp theo cũng phải là một cột gồm 10 ô. Nếu không, hàm SUMPRODUCT sẽ báo lỗi. 

Lợi ích quan trọng của việc sử dụng hàm SUMPRODUCT

Hàm SUMPRODUCT không chỉ giúp xử lý phép tính nhân tổng mà còn mang lại nhiều lợi ích thiết thực khác. Cụ thể như là:

• Tính toán đơn giản, nhanh chóng & tiết kiệm thời gian

Với hàm SUMPRODUCT, bạn chỉ cần một công thức duy nhất để thực hiện phép nhân từng phần tử trong mảng rồi tính tổng kết quả. Chẳng hạn như nhân số lượng với giá tiền trong báo cáo bán hàng hoặc tính tổng doanh thu mà không cần dùng nhiều bước trung gian khác.

• Xử lý nhiều điều kiện (AND/OR) linh hoạt

Hàm SUMPRODUCT có thể kết hợp với các hàm khác như AND hoặc OR để xử lý các mảng điều kiện. Đồng thời, nó còn giúp bạn tính tổng, đếm theo nhiều tiêu chí mà không cần hàm SUMIFS phức tạp. 

• Giúp công thức tính toán trở nên gọn hơn

Nhiều trường hợp tính số trung bình, lọc dữ liệu theo nhiều điều kiện hoặc đếm các giá trị logic, bạn phải dùng nhiều công thức. Nhưng nếu sử dụng hàm SUMPRODUCT, bạn sẽ có một công thức gọn hơn, thay vì phải lồng nhiều hàm vào với nhau.

• Kết hợp nhiều hàm khác, xử lý bài toán phức tạp

Hàm SUMPRODUCT còn có thể kết hợp linh hoạt với các hàm LEN, COUNTIF, ABS, MOD, SEARCH,… Khi kết hợp với các hàm khác, bạn sẽ thực hiện xử lý nhiều bài toán phức tạp khác. Như là đếm ký tự, đếm số chẵn/lẻ, tính tổng giá trị tuyệt đối,…

Cách sử dụng hàm SUMPRODUCT trong Google Sheets cơ bản

Để hiểu rõ hơn về cách sử dụng hàm SUMPRODUCT trong Google Sheets, các bạn có thể tham khảo những ví dụ áp dụng công thức cụ thể sau đây. 

Giả sử, bạn có bảng dữ liệu cụ thể như sau:

Hàm SUMPRODUCT phép cộng

Hàm SUMPRODUCT có chức năng nhân và cộng. Nhưng khi truyền một mảng dữ liệu, hàm SUMPRODUCT sẽ tự động tính tổng của tất cả các giá trị trong mảng đó. 

*Yêu cầu: Bạn cần tính tổng của số lượng + đơn hàng cho từng dòng

Công thức:

=SUM((B2:B4 + C2:C4))

Trong đó:

• B2:B4 + C2:C4 tạo mảng [10+5, 8+7, 15+4] → [15, 15, 19], rồi SUM về 49.

Kết quả: 

Hàm SUMPRODUCT phép trừ

SUMPRODUCT không có chức năng trừ trực tiếp. Tuy nhiên, bạn có thể thực hiện phép trừ bằng cách nhân một mảng với -1 và sau đó cộng vào mảng còn lại.

*Yêu cầu: Tính tổng hiệu giữa Đơn giá và Số lượng cho mỗi dòng.

Công thức:

=SUMPRODUCT((C2:C4 – B2:B4))

Trong đó:

• Tạo mảng [5‑10, 7‑8, 4‑15] = [‑5, ‑1, ‑11], rồi cộng → kết quả là ‑17

Kết quả: 

Hàm SUMPRODUCT phép nhân

Phép nhân trong hàm SUMPRODUCT được thực hiện trên các phần tử tương ứng của từng mảng dữ liệu. Sau đó, các kết quả sẽ được tiến hành cộng lại. 

*Yêu cầu: Tính tổng doanh thu = Số lượng * Đơn giá trên tất cả các dòng.

Công thức:

=SUMPRODUCT(B2:B4; C2:C4)

Trong đó:

• Tích mỗi dòng như sau 105 =50, 87 =56, 15*4 =60. Sau đó cộng tổng → 166

Kết quả:

Hàm SUMPRODUCT phép chia

Bạn có thể dùng hàm SUMPRODUCT để chia tương ứng rồi cộng nếu đảm bảo không có giá trị 0.

*Yêu cầu: Giả sử Đơn giá là mẫu, bạn muốn tính tổng tỷ lệ đơn giá / số lượng.

Công thức:

=SUMPRODUCT(C2:C4 / B2:B4)

Trong đó:

• Tỷ lệ mỗi dòng: 5/10 =0.5, 7/8 =0.875, 4/15 ≈0.2667; tổng ≈ 1.6417.

Kết quả:

Hàm SUMPRODUCT cho một tiêu chí

Bạn có thể dùng hàm SUMPRODUCT để đếm hoặc tính tổng theo một điều kiện logic duy nhất. 

Giả sử, bạn có bảng dữ liệu cụ thể như sau:

*Yêu cầu: Tính tổng số lượng các mặt hàng ở khu vực miền Trung

Công thức:

=SUMPRODUCT((D2:D5=”Trung”)*B2:B5)

Trong đó:

• (D2:D5=”Trung”) tạo mảng [1, 0, 0,1], nhân với [10,8,15,12] → [10, 0, 0, 12], tổng = 22

Kết quả:

Hàm SUMPRODUCT cho nhiều tiêu chí

Bạn có thể sử dụng hàm SUMPRODUCT để xử lý nhiều điều kiện và lọc dữ liệu chính xác hơn. 

*Yêu cầu: Tính tổng số lượng cho sản phẩm ở vùng Trung và có Đơn giá ≥ 5.

Công thức:

=SUMPRODUCT((D2:D5=”Trung”) * (C2:C5>=5) * B2:B5)

Trong đó:

• Điều kiện vùng Trung [1,0,0,1] 
• Điều kiện giá ≥5 [1,1,0,0]; 
• Nhân cả hai: [1*1, 0*1, 1*0, 1*0] = [1,0,0,0], rồi nhân số lượng [10,8,15,12], tổng = 10.

Kết quả:

Hàm SUMPRODUCT cho cùng lúc nhiều điều kiện

Bạn có thể sử dụng hàm SUMPRODUCT để xử lý nhiều điều kiện AND/OR khác nhau. 

*Yêu cầu: Tính tổng số lượng cho mặt hàng là Microsoft 365 hoặc Zoho Workspace ở vùng miền Trung.

Công thức:

=SUMPRODUCT(((A2:A5=”Microsoft 365″) + (A2:A5=”Zoho Workspace”)) * (D2:D5=”Trung”) * B2:B5)

Trong đó:

• Điều kiện (A2:A5=”Microsoft 365″) + (A2:A5=”Zoho Workspace”) tạo mảng: [1+0, 0+0, 0+0, 0+1] = [1,0,0,1]; 
• Điều kiện vùng Trung = [1,0,0,1]; 
• Nhân: [1,0,0,1] * [1,0,0,1] = [1,0,0,1]; nhân số lượng: [10,8,15,12] → tổng = 22. 
• Đây là cách xử lý điều kiện phức tạp với OR và AND kết hợp

Kết quả:

Cách sử dụng hàm SUMPRODUCT trong Google Sheets nâng cao

Hàm SUMPRODUCT trong Google Sheets sử dụng theo dạng cơ bản khá dễ. Nhưng nếu bạn muốn ứng dụng nâng cao hơn thì có thể kết hợp hàm với các hàm khác, để giải quyết nhiều bài toán phức tạp hơn. 

Hàm SUMPRODUCT kết hợp sử dụng với hàm LEN

Bạn có thể kết hợp hàm SUMPRODUCT với hàm LEN để tổng hợp số ký tự trong một cột. 

Ví dụ với bảng dữ liệu sau:

*Yêu cầu: Tính tổng số ký tự trong một danh sách các sản phẩm.

Công thức:

=SUMPRODUCT(LEN(A2:A5))

Trong đó:

• LEN(A2:A5): tính số ký tự của từng ô trong phạm vi A2:A5.
• SUMPRODUCT(…): cộng tất cả giá trị trả về từ LEN, tức là tổng số ký tự.

Kết quả:

Hàm SUMPRODUCT kết hợp sử dụng với hàm COUNTIF

Sự kết hợp giữa hàm SUMPRODUCT và COUNTIF giúp kiểm tra số lần xuất hiện của một giá trị trong danh sách và tính tổng số lần khớp với điều kiện. Cách kết hợp này thường được dùng để xử lý các điều kiện không đơn giản và áp dụng trên nhiều hàng. 

Ví dụ cụ thể với bảng dữ liệu sau đây:

*Yêu cầu: Tính số lần học sinh Trịnh Thị Hoa trong danh sách được chấm điểm A.

Công thức:

=SUMPRODUCT((A2:A6=”Trịnh Thị Hoa”)*(B2:B6=”A”))

Trong đó:

• (A2:A6=”Trịnh Thị Hoa”): tạo mảng TRUE/FALSE với điều kiện tên là “Trịnh Thị Hoa”.
• (B2:B6=”A”): tạo mảng TRUE/FALSE với điều kiện điểm là “A”.
• Hai điều kiện nhân với nhau => chỉ những dòng thỏa cả hai điều kiện mới cho giá trị 1.
• SUMPRODUCT(…): cộng tất cả kết quả 1 => cho ra số lần Trịnh Thị Hoa được điểm A.

Kết quả:

Các lỗi thường gặp khi dùng hàm SUMPRODUCT và cách khắc phục

Khi dùng hàm SUMPRODUCT cũng dễ xảy ra các lỗi nếu bạn không chú ý đến các quy tắc cơ bản. Dưới đây là hai lỗi phổ biến nhất khi sử dụng hàm SUMPRODUCT trong Google Sheets và cách khắc phục hiệu quả nhất.

Lỗi #VALUE!

Lỗi #VALUE! thường xảy ra khi các mảng dữ liệu sử dụng không có cùng kích thước. Bởi quy tắc quan trọng nhất của hàm SUMPRODUCT là tất cả các mảng phải có cùng số hàng và số cột. 

Ví dụ công thức SUMPRODUCT(A2:A10, B2:B11). Lúc này, Google Sheets sẽ báo lỗi vì dải ô A2:A10 có 9 hàng, trong khi B2:B11 có 10 hàng.

Cách khắc phục của lỗi này là bạn phải kiểm tra và đảm bảo rằng tất cả các dải ô trong công thức của bạn đều có cùng kích thước.

Lỗi #REF!

Lỗi #REF! thường xuất hiện khi tham chiếu đến một ô hoặc một dải ô không tồn tại. Ví dụ như khi bạn đã xóa một cột hoặc một hàng mà công thức đang tham chiếu đến. 

Để khắc phục lỗi này thì bạn chỉ cần kiểm tra lại các tham chiếu trong công thức để đảm bảo rằng chúng vẫn tồn tại. Nếu bạn đã xóa các ô thì hãy cập nhật lại công thức và các dải ô mới. 

Lưu ý khi dùng hàm SUMPRODUCT trong Google Sheets

Trong quá trình sử dụng hàm SUMPRODUCT trong Google Sheets, để tránh gặp phải các lỗi thì bạn cũng cần nên lưu ý một số vấn đề quan trọng như sau:

• Thứ nhất là về vấn đề kích thước mảng, đều phải đảm bảo có cùng kích thước.
• Thứ hai là về vấn đề kiểu dữ liệu. Nếu các mảng dữ liệu chứa văn bản, bạn cần sử dụng các điều kiện logic để chuyển chúng thành giá trị số.
• Thứ ba là về vấn đề hiệu suất. Đối với các tập dữ liệu lớn, hàm SUMPRODUCT có thể tiêu tốn nhiều tài nguyên hơn so với các hàm đơn giản khác. Trong trường hợp đó, bạn có thể cân nhắc sử dụng các giải pháp thay thế như hàm mảng hoặc các hàm khác phù hợp hơn.

>>> Xem thêm: Hàm AND và OR trong Google Sheets

Lời kết

Như vậy, bài viết trên đây đã giúp các bạn hiểu rõ thế nào và cách sử dụng hàm SUMPRODUCT trong Google Sheets cụ thể ra sao? Hy vọng rằng, những thông tin này sẽ trở nên hữu ích cho các bạn trong quá trình áp dụng vào tính toán dữ liệu.

Ngoài ra, người dùng nên sở hữu ngay ứng dụng Google Sheet chính hãng với việc mua gói Google Workspace bản quyền.

Nếu trong quá trình sử dụng hàm SUMPRODUCT còn gặp khó khăn gì, các bạn đừng ngần ngại liên hệ với GCS Việt Nam qua các kênh dưới đây để được đội ngũ kỹ thuật hỗ trợ nhé. 

• Fanpage: GCS – Google Cloud Solutions 
• Hotline: 024.9999.7777