Cách sử dụng hàm sumproduct

Để phục vụ cho quá trình tính toán thì người dùng đang sử dụng rất nhiều đến các hàm trong Excel. Trong đó, sumproduct là một trong những hàm đang được sử dụng phổ biến nhất hiện nay. Vậy hàm sumproduct là gì, được sử dụng đi làm gì? Các bạn hãy cùng chúng tôi tìm hiểu chi tiết trong nội dung bài viết dưới đây nhé.

” Video Hướng dẫn cách sử dụng hàm Sumproduct trong Excel từ Gà thành Pro”

==> Xem thêm: Hàm round là hàm gì? Hướng dẫn cách sử dụng hàm round làm tròn số trong excel, access, sql

Hàm Sumproduct là gì? Dùng để làm gì?

Hàm Sumproduct trong Excel là một sự kết hợp giữa 2 hàm SUM (tính tổng) và hàm PRODUCT (tính nhân). Việc kết hợp này sẽ trả về giá trị tổng của các tích của một dãy số đã cho trong Excel. Từ đó giúp quá trình nhân tích của các phép cộng với nhau diễn ra một cách nhanh chóng và chính xác nhất.

Hàm sumproduct tính tổng nhiều điều kiện hiệu quả trong Excel

Cú pháp hàm SUMPRODUCT:

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

Trong đó:

• Array1: đối số mảng đầu tiên muốn nhân rồi tính tổng. Đây là đối số bắt buộc phải có
• Array2, Array3: các đối số này có thể có hoặc không và trong phạm vi từ 2 đến 255.

Lưu ý:

• Các đối số trong hàm SUMPRODUCT đều có cùng số hàng, số cột. Nếu không cùng hàng hoặc cột thì hàm SUMPRODUCT sẽ báo giá trị lỗi #VALUE!
• Các ô dữ liệu nếu không có con số cụ thể thì sẽ coi đó là số 0.
• Với chương trình Excel 2003 thì có thể thêm tối đa 30 mảng, đối với những chương trình từ Excel 2007 trở lên thì có thể dùng tối đa 255 mảng.

Hàm sumproduct trong excel 2010 đối số mảng trong phạm vi từ 2 đến 255

Cách sử dụng hàm Sumproduct

Để có thể giúp các bạn hình dung cụ thể cách sử dụng hàm Sumproduct thì chúng ta hãy cùng theo dõi ví dụ dưới đây:

Nội dung: Bảng thông kế đơn hàng của 1 cửa hàng. Với những số liệu trên chúng ta cần phải tính tổng doanh thu của cửa hàng đó. 

Để có thể tính doanh thu của cửa hàng trên chúng ta sử dụng hàm Sumproduct với công thức:

=SUMPRODUCT(B2:B6,C2:C6)

Rồi nhấn phím Enter để thực hiện phép tính 

Giải thích: 

Khi sử dụng hàm SUMPRODUCT trong phép tính thực chất là thực hiện:

 (30 * 25) + (21 * 36) + (55 * 60) + (69 * 18) + (33*108)= 9612

Tuy nhiên nếu trong quá trình thực hiện có sai sót kết quả sẽ báo lỗi #VALUE!

Chỉ với một vài thao tác đơn giản, các bạn đã nhanh chóng có được một kết quả chính xác. Đây chỉ là một ví dụ đơn giản, người dùng có thể sử dụng để thực hiện tính toán với nhiều mảng đối số trong bảng dữ liệu.

Nếu gặp trường hợp này, người dùng nên thực hiện kiểm tra và rà soát lại công thức

Trên đây là toàn bộ lý thuyết và ví dụ về cách dùng hàm Sumproduct. Hy vọng với những gì chia sẻ trên sẽ giúp bạn sử dụng hàm SUMPRODUCT thành thạo giúp công việc luôn đạt hiệu quả cao. 

Cũng lâu ghê mình chưa viết bài về Excel đấy nhỉ. Thời gian vừa qua mình cũng bận việc với phải dành thời gian thay đổi địa chỉ cho blog và chỉnh sửa lại blog 1 chút. Hôm nay cuối tuần rảnh mình cố gắng ngồi viết bài để cho các bạn có bài để đọc 😀

Trong bài này mình sẽ viết hướng dẫn chi tiết về cách dùng hàm SUMPRODUCT trong Excel qua ví dụ cụ thể để các bạn biết cách sử dụng hàm này khi làm việc. Về hàm SUMPRODUCT thì nó là hàm dùng để tính tổng các tích của các phần tử số trong các mảng. Về cơ bản bạn chỉ hiểu sơ sơ như vậy thôi còn về cách sử dụng hàm SUMPRODUCT trong Excel như nào thì chúng ta đi vào tìm hiểu công thức và ví dụ cụ thể dưới đây.

Hàm SUMPRODUCT là hàm được dùng khá nhiều trong kế toán chính vì thế nó có thể áp dụng được trên hầu hết các phiên bản Excel như Excel 2007, 2010, 2013, 2016 và kể cả Excel cho điện thoại iPhone, Android,…Về cách dùng thì trên các phiên bản nó đều tương tự nhau, ở đây mình hướng dẫn các bạn làm trên Excel 2016 còn bạn nào dùng các phiên bản khác cũng làm tương tự không có gì khác cả.

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

Chú ý: Tùy theo cài đặt của từng máy tính mà bạn dùng dấu phân cách là dấu phẩy (,) hoặc chấm phẩy (;) nhé.

• Array1: Mảng đầu tiên bạn muốn nhân các thành phần của nó rồi cộng tổng. Đây là giá trị bắt buộc.
• [array2], [array3],…: Đây là các mảng từ 2 đến tối đa 255 mà bạn muốn nhân các thành phần của nó rồi cộng tổng. Đây là giá trị tùy chọn.

Lưu ý

• Tất cả các đối số trong hàm SUMPRODUCT phải có cùng số hàng, số cột. Nếu không, hàm SUMPRODUCT trả về giá trị lỗi #VALUE! .
• Bất kỳ ô dữ liệu nào trong mảng không có dạng số thì hàm SUMPRODUCT sẽ coi đó là số 0.
• Đối với Excel 2003 bạn có thể thêm tối đa 30 mảng, còn từ phiên bản Excel 2007 trở lên bạn có thể dùng tối đa 255 mảng.

Tham khảo thêm

Ví dụ 1: Cách dùng hàm SUMPRODUCT cơ bản

Để hiểu về cách dùng hàm SUMPRODUCT cơ bản chúng ta cùng làm ví dụ sau đây để hiểu về cách sử dụng hàm này nhé.

Yêu cầu: Chúng ta có 1 bảng thống kê đơn hàng ở 1 cửa hàng. Chúng ta phải tính tổng doanh thu của tất cả các đơn hàng trong cửa hàng đó. Chúng ta sẽ áp dụng hàm SUMPRODUCT để tính tổng doanh thu.

Như trong hình trên bạn cũng thấy, để tính được tổng doanh thu chúng ta cứ lấy (Số lượng bán * Đơn giá) rồi cộng tổng vào là ra. Nhưng ở đây chúng ta có sự hỗ trợ của hàm SUMPRODUCT thì sao phải nhân rồi cộng tổng bằng chi cho vất vả.

Tại ô F4 chúng ta nhập vào công thức hàm SUMPRODUCT như sau: =SUMPRODUCT(D4:D9,E4:E9) = 1657.5

=> Diễn giải chút về cơ chế của hàm SUMPRODUCT như sau: Hàm sẽ lấy giá trị trong ô đầu tiên của mảng đầu tiên nhân với giá trị trong ô đầu tiên của mảng thứ 2, rồi lấy giá trị trong ô thứ 2 của mảng đầu tiên nhân với giá trị trong ô thứ 2 của mảng thứ 2, nó cứ làm thế cho đến khi nhân hết các giá trị trong mảng. Sau khi đã nhân hết các thành phần trong mảng nó sẽ cộng tổng lại và cho ra kết quả.

Để các bạn hiểu hơn mình sẽ dựa vào ví dụ trên và nó sẽ có công thức về cơ chế tính toán như sau: =D4*E4 + D5*E5 + D6*E6 + D7*E7 + D8*E8 + D9*E9

Ngoài ra, kết quả trên nó cũng tương đương khi bạn dùng hàm SUM để tính với công thức như sau: =SUM(D4*E4,D5*E5,D6*E6,D7*E7,D8*E8,D9*E9)=1657.5

Ví dụ 2: Cách dùng hàm SUMPRODUCT với 1 điều kiện

Với hàm SUMPRODUCT chúng ta sẽ không tận dụng hết khả năng của nó nếu chỉ dùng để tính toán như ví dụ cơ bản trên. Để hàm này phát huy hết khả năng của nó thì bạn cần cho nó thêm những yêu cầu khó hơn bằng cách chèn thêm điều kiện vào cho nó. Ở ví dụ này mình sẽ bắt đầu với 1 điều kiện để bạn làm quen dần nhé.

Yêu cầu: Tính tổng số sản phẩm bán ra có số lượng KHÔNG đạt chỉ tiêu.

Ở đây, nếu bạn không biết dùng hàm SUMPRODUCT bạn cũng có thể sử dụng hàm COUNTIF để đếm, nhưng nó sẽ rất vất vả nếu trong bảng tính có tới hàng nghìn đầu mục. Còn khi bạn đã biết cách dùng hàm SUMPRODUCT rồi thì việc tính toàn đơn giản như sau:

=SUMPRODUCT(–(E4:E9 = 4

hoặc công thức

=SUMPRODUCT((E4:E9 = 4

Trong công thức đầu tiên, bạn thấy có 2 dấu trừ (–) về mặt kỹ thuật thì đây là hai toán tử đơn phân, nó giúp biến TRUE thành 1 và FALSE thành 0.

Ví dụ trong công thức =SUMPRODUCT(–(E4:E9E4:E9 rồi ấn phím F9 thì công thức sẽ chuyển thành =SUMPRODUCT(–({TRUE;TRUE;FALSE;TRUE;FALSE;TRUE})) và 2 dấu trừ nó sẽ giúp công thức có dạng tương tự là: =SUMPRODUCT({1;1;0;1;0;1}) = 4

Ví dụ 3: Cách dùng hàm SUMPRODUCT với nhiều điều kiện

Ở đây, chúng ta sẽ sử dụng lại ví dụ 2 nhưng sẽ thêm 1 điều kiện nữa cho nó khó 😀

Yêu cầu: Tính tổng số sản phẩm SONY bán ra có số lượng KHÔNG đạt chỉ tiêu.

Sử dụng hàm SUMPRODUCT chúng ta có công thức tính như sau: =SUMPRODUCT((B4:B9=”SONY”)*(E4:E9>F4:F9))

Trong công thức này bạn để ý dấu * là dùng để nối 2 điều kiện với nhau nhé. Ngoài ra, bạn có thể sử dụng công thức tương tự như trong ví dụ 2 như sau: =SUMPRODUCT(-(B4:B9=”SONY”),-(E4:E9>F4:F9))

Cả 2 công thức trên đều cho ra kết quả giống nhau nhé.

Trên đây là các ví dụ về cách sử dụng hàm SUMPRODUCT để áp dụng trong công việc giúp bạn làm việc dễ dàng và hiệu quả hơn.

Để sử dụng thành thạo được hàm SUMPRODUCT thì đòi hỏi bạn cần luyện tập thật nhiều về hàm này. Dưới đây, mình sẽ đính kèm file ví dụ về hàm SUMPRODUCT các bạn tải về và thực hành nhé.

Download file ví dụ về hàm SUMPRODUCT

Lời kết

Trên đây là toàn bộ lý thuyết và ví dụ về hàm SUMPRODUCT rất chi tiết. Hi vọng với những gì mình chia sẻ sẽ giúp bạn sử dụng được thành thạo hàm SUMPRODUCT để vận dụng được vào trong công việc của bạn.

Nếu thấy bài viết hay hãy like và share cho mọi người nhé. Và có thắc mắc gì hãy để lại ý kiến dưới khung bình luận để được giải đáp nhé. Nhớ theo dõi chuyên mục thủ thuật Excel trên NguyenHung.Net để học nhiều hơn các hàm Excel nhé. Chúc các bạn thành công!