Thủ thuật

Hướng dẫn cách sử dụng hàm SUMPRODUCT trong Google Sheet tính tích tổng đơn giản

Trương Nguyễn Anh Thư

04-05-2023, 9:08 am

Hàm SUMPRODUCT là một trong những hàm cơ bản được sử dụng phổ biến trong Google Sheet, hàm này thường được dùng để tính tích tổng. Tuy nhiên, bạn có thực sự biết cách sử dụng hàm SUMPRODUCT này hay chưa? Vậy nên để có thể hiểu rõ hơn về hàm SUMPRODUCT này, mời các bạn cùng theo dõi bài viết dưới đây!

Hàm SUMPRODUCT là hàm gì? Ứng dụng của hàm SUMPRODUCT này trong Google Sheet

- Hàm SUMPRODUCT là hàm gì?

Hàm SUMPRODUCT là một hàm giúp bạn có thể nhân tích của từng cặp phần tử, sau đó cộng tổng chúng lại với nhau.

- Công thức của hàm SUMPRODUCT

Công thức hàm:

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

Trong đó:

+ array1: Đây là đối số mảng đầu tiên mà bạn muốn nhân tích các thành phần của nó rồi sau đó cộng tổng.

+ [array2]; [array3];..: Đây là các đối số mảng từ 2 đến 255 mà bạn muốn nhân tích các thành phần của nó rồi sau đó cộng tổng.

- Ví dụ cơ bản về hàm SUMPRODUCT

Hàm SUMPRODUCT được dùng để tính tổng tiền các mặt hàng theo như bảng dữ liệu được cho dưới đây.

Công thức hàm:

=SUMPRODUCT(B3:B7;C3:C7)

Ý nghĩa của công thức:

+ B3:B7;C3:C7 : biểu thị giá trị của mảng Số lượng và mảng Đơn giá.

+ Tổng số tiền= 3*150000+5*140000+7*120000+5*170000+4*200000.

Ví dụ về hàm SUMPRODUCT như hình trên

- Ứng dụng của hàm SUMPRODUCT trong Google Sheet:

Hàm SUMPRODUCT được ứng dụng vô cùng rộng rãi trong việc kế toán, nó được dùng để tính tổng theo điều kiện, tính tiền lãi, trong kế toán kho hay tính tiền lương,....

Các cách sử dụng hàm SUMPRODUCT trong Google Sheet.

- Sử dụng hàm SUMPRODUCT với một tiêu chí

Hàm SUMPRODUCT được dùng để tính tổng số tiền của những sản phẩm trong hình bên dưới.

Công thức hàm:

=SUMPRODUCT(B3:B6;C3;C6)

Ý nghĩa của công thức:

+ B3:B6: đây là số lượng của sản phẩm.

+ C3:C6: biểu thị đơn giá của sản phẩm.

+ Tổng số tiền= 3*10000+5*4000+7*5000+2*15000.

Tính tổng tiền của sản phẩm như hình trên

- Sử dụng hàm SUMPRODUCT với nhiều tiêu chí

Hàm SUMPRODUCT còn được dùng để tính Tổng tiền thuế của những sản phẩm được mô tả trong bảng dữ liệu trong hình.

Công thức hàm:

=SUMPRODUCT(B3:B6;C3:C6;D3:D6)

Ý nghĩa của công thức:

+ B3:B6;C3:C6;D3:D6 đây là giá trị tương ứng của Số lượng, Đơn giá và thuế.

+ Tổng số tiền thuế= (3*150000*1%)+(5*140000*0.5%)+(7*120000*1%)+(5*170000*0.5%).

Tính tổng tiền thuế của sản phảm như hình trên

- Sử dụng hàm SUMPRODUCT với nhiều điều kiện

Sử dụng hàm SUMPRODUCT với nhiều điều kiện để tính tổng số tiền của những sản phẩm có RAM 8Gb và Ổ cứng 512 SSD.

Công thức hàm:

=SUMPRODUCT( B3:B6;C3:C6;(D3:D6=”8Gb”);(E3:E6=”512 SSD”) )

Ý nghĩa của công thức:

+ D3:D6= "8Gb"; E3:E6= "512 SSD" biểu thị rằng hàm sẽ xét điều kiện trong các mảng, nếu thỏa sẽ trả về kết quả 1 (TRUE).

+ Tổng số tiền chỉ tính giá trị sản phẩm có RAM 8Gb và Ổ cứng 512 SSD.

Tính tổng tiền giá trị sản phẩm có RAM 8 Gb và ổ cứng 512 SSD như hình trên

Một vài phép toán số học được hàm SUMPRODUCT hỗ trợ

Hàm SUMPRODUCT có thể hỗ trợ một số toán tử như cộng, trừ, nhân, chia. Để sử dụng hàm SUMPRODUCT để hỗ trợ, bạn thay dấu “;” bằng (+ ,- , *, /), sau đó hàm sẽ tính toán tổng như bình thường.

- Với phép cộng

Ví dụ minh họa cho phép toán cộng: Bạn hãy dùng hàm SUMPRODUCT để tính tổng doanh thu.

Công thức hàm:

=SUMPRODUCT(B4:B6+C4:C6)

Ý nghĩa của công thức:

+ Lúc này hàm SUMPRODUCT sẽ thực hiện bài toán cộng 2 mảng rồi sau đó mới tiến hành cộng tổng chúng lại với nhau.

+ Kết quả cho ra của tổng doanh thu= (520+550)+(450+470)+(200+250).

Ví dụ về phép cộng như hình trên

- Với phép trừ

Ví dụ minh họa cho phép trừ: Hãy tính tiền tổng tiền lãi của những sản phẩm dựa trên những hàm đã học.

Công thức hàm:

=SUMPRODUCT((B3:B7)-(C3:C7))

Ý nghĩa của công thức:

+ Hàm SUMPRODUCT sẽ trừ đi 2 mảng rồi sau đó mới cộng chúng lại để có được kết quả cho Tổng tiền lãi.

+ Tổng số tiền lãi= (540-350)+(420-200)+(450-230)+(750-400)+(350-100).

Ví dụ về phép trừ như hình trên

- Với phép nhân

Ví dụ minh họa cho phép nhân: Hãy tính tổng số tiền sản phẩm được cho trong hình.

Công thức hàm:

=SUMPRODUCT(B4:B6*C4:C6)

Ý nghĩa của công thức:

+ Hàm SUMPRODUCT sẽ được thực hiện phép tính như bình thường.

+ Tổng số tiền sản phẩm= 10*12000+7*2000+6*5000+7*7000+3*7000.

Ví dụ về phép nhân như hình trên

- Với phép chia

Ví dụ minh họa cho phép chia: Bạn hãy tính tổng số thùng để chứa của những sản phẩm được cho trong hình.

Công thức hàm:

=SUMPRODUCT(B3:B6/C3:C6)

Ý nghĩa của công thức:

+ Hàm SUMPRODUCT này sẽ chia 2 mảng rồi mới cộng chúng lại để có thể cho ra kết quả cuối cùng.

+ Tổng số thùng để chứa = 600/30+400/20+240/24+200/20.

Ví dụ về phép chia như hình trên

Các lưu ý quan trọng khi sử dụng hàm SUMPRODUCT trong Google Sheet

- Các mảng cần xem xét đến phải có kích cỡ bằng nhau (cùng số hàng và cùng số cột) nếu không, hàm sẽ báo lỗi #VALUE!.

Lỗi #VALUE! trong hàm SUMPRODUCT như hình trên

- Hàm SUMPRODUCT sẽ coi những ô dữ liệu trống hoặc không chứa dữ liệu số là 0 khi nó thực hiện phép tính.

Lưu ý khi ô dữ liệu trống như hình trên

Một số hàm kết hợp với hàm SUMPRODUCT

- Hàm SUMPRODUCT kết hợp cùng với hàm LEN

Hàm kết hợp giữa SUMPRODUCT và LEN sẽ cho phép bạn đếm tổng số ký tự trong một mảng.

Công thức hàm:

=SUMPRODUCT(LEN(range))

Ví dụ minh họa cho hàm kết hợp SUMPRODUCT và LEN: Bạn hãy đếm số ký tự trong mảng Size.

Công thức hàm:

=SUMPRODUCT(LEN(B3:B7))

Ý nghĩa của công thức:

+ Hàm kết hợp này sẽ đếm toàn bộ ký tự có trong mảng Size.

Ví dụ kết hợp hàm SUMPRODUCT với hàm LEN như hình trên

- Hàm SUMPRODUCT kết hợp cùng với hàm COUNTIF

Hàm kết hợp giữa hàm SUMPRODUCT cùng hàm COUNTIF được sử dụng để đếm các giá trị trùng lặp và duy nhất trong một mảng hoặc giữa 2 mảng với nhau.

Công thức hàm:

=SUMPRODUCT(COUNTIF(range;criteria)

Trong đó:

+ Range: Biểu thị phạm vi chứa các ô cần đếm

+ Criteria: Đây là điều kiện để tìm ra ô nào sẽ được chọn đếm.

Ví dụ minh hoạ cho hàm kết hợp SUMPRODUCT và COUNTIF: Bạn hãy đếm số ký tự “Trắng” có trong mảng Size.

Công thức hàm:

=SUMPRODUCT(COUNTIF(B3:B7;”Trắng”))

Ý nghĩa của công thức:

+ Hàm kết hợp này sẽ đếm số từ "Trắng" trùng lặp trong mảng Size.

Ví dụ kết hợp hàm SUMPRODUCT với hàm COUNTIF như hình trên

Các cách khắc phục lỗi #VALUE trong hàm SUMPRODUCT

- Lỗi do các tham số trong mảng không có cùng kích thước với nhau

Lỗi này xuất hiện có thể là do bạn đã nhập dư số ô của cột hoặc hàng làm cho kích thước của các tham số mảng không đồng đều. Bạn có thể khắc phục lỗi này bằng cách kiểm tra lại số ô và điều chỉnh lại cho tương ứng.

- Lỗi #VALUE xuất hiện do một hoặc nhiều ô trong dải ô có chứa văn bản

Khi một ô hoặc nhiều ô trong dải ô có chứa văn bản, hàm SUMPRODUCT sẽ không hiểu và nó sẽ trả kết quả #VALUE. Và để có thể khắc phục tình trạng này thì bạn chỉ cần xóa văn bản trong ô và điền lại vào ô đó bằng một kiểu dữ liệu thích hợp.

Những câu hỏi thường gặp khi sử dụng hàm SUMPRODUCT trong Google Sheet

Hàm SUMPRODUCT hoạt động như thế nào trong Google Sheet?

Hàm SUMPRODUCT là một hàm tính tích tổng trong Google Sheet, hàm này là sự kết hợp giữa hàm SUM và hàm PRODUCT. Khi hoạt động, hàm SUMPRODUCT sẽ nhân các phần tử trong mảng lại với nhau rồi mới cộng các tích đó lại và cho ra kết quả cuối cùng.

Dấu nhân và dấu chấm phẩy của hàm SUMPRODUCT khác nhau ở đâu?

Khi trong một ô hoặc nhiều ô trong dải ô của mảng có chứa ký tự, nếu cú pháp là dấu nhân thì hàm sẽ trả về lỗi #VALUE!, còn cú pháp là dấu chấm phẩy, hàm sẽ cho kết quả 0 tại ô đó và thực hiện phép tính như bình thường.

Phân biệt giữa dấu nhân và dấu chấm phẩy như hình trên

Trên đây là những thông tin hữu ích về hàm SUMPRODUCT trong Google Sheet. Mong rằng bài viết trên sẽ mang lại cho bạn những kiến thức cần thiết. Cảm ơn các bạn đã theo dõi!