Cách sử dụng SUMPRODUCT để tính tổng nhiều điều kiện trong Excel

 

Để thực hiện việc đếm nhiều điều kiện, ngoài sự hữu dụng của hàm COUNTIFS thì bạn có biết, hàm SUMPRODUCT cũng làm được nhiệm vụ tương tự như người đồng nghiệp của mình. Qua bài viết này, Gitiho.com sẽ chỉ cho các bạn cách dùng hàm SUMPRODUCT để đếm nhiều điều kiện trong Excel nhé!

Nguyên tắc đếm của hàm SUMPRODUCT

Hàm SUMPRODUCT là một hàm công thức thuộc nhóm dữ liệu dạng mảng. Cấu trúc của hàm này sẽ là các mảng sau:

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

Giá trị của hàm SUMPRODUCT đến từ mảng array trong công thức của nó bởi phạm vi hoạt động của mảng này vô cùng linh hoạt, có thể gộp được nhiều vùng dữ liệu được nối lại hoặc một phần tử của một vùng dữ liệu. Bên cạnh đó việc tính toán theo mảng Array thế này sẽ không gặp giới hạn.

Để đạt điều kiện đếm thì mỗi phần tử trong 1 vùng phải thỏa mãn điều kiện thì được tính là 1. Kết quả mà mỗi lần đếm đưa ra là tổng các giá trị thoả mãn điều kiện trên. Tương tự trong việc đếm theo điều kiện sẽ khá giống với việc tính tổng các giá trị thoả mãn điều kiện. Điểm khác biệt duy nhất ở đây là mỗi giá trị thoả mãn không phụ thuộc vào bất kỳ một giá trị nào mà chỉ có giá trị là 1.

Xem thêm: Hướng dẫn cách dùng hàm SUMPRODUCT để đếm trong Excel

Áp dụng hàm SUMPRODUCT để tính tổng nhiều điều kiện

Ví dụ 1: dùng hàm SUMPRODUCT để đếm

Ở đây chúng ta sẽ phải xét hai điều kiện đếm:

• Với mặt hàng "Cam" ta phải xét trong cột "Tên hàng"  với điều kiện là "Cam".
• Thời gian là "Tháng 6" sẽ xét trong cột "Ngày" và mỗi ô trong cột "Ngày" chỉ xét giá trị tháng là 6.

Xét điều kiện đầu tiên: Thông thường khi sử dụng Excel, ta sẽ gõ như sau:

C2="Cam"

C3="Cam"

....

C18="Cam"

• Để sử dụng dạng mảng cũng như để tiết kiệm thời gian, chúng ta nên viết thành C2:C18="Cam".
• Với cách viết này thì Excel vẫn hiểu ý của người dùng là xét mỗi giá trị C2:C18, ô nào có giá trị là cam.

Xét điều kiện thứ 2: Để xét tháng trong một hàng hoặc cột có đầy đủ ngày, tháng, năm thì ta dùng hàm MONTH. Trong trường hợp này ta muốn lấy thời gian là tháng 6 thì sẽ phải viết như sau:

=MONTH(A2:A18)=6 

Thông thường, hàm Month sẽ xét trên một giá trị, vậy nên ta sẽ thường thấy:

Month(A2)=6

Month(A3)=6

.…

Month(A18)=6

Rõ ràng là theo cách này thì các thao tác của chúng ta tốn nhiều thời gian hơn mà vẫn ra chung một kết quả như vậy. Hàm SUMPRODUCT thuộc nhóm hàng bảng tên nên chúng ta hoàn toàn sử dụng được cách viết tắt như trên.

Dựa trên tính chất trên ta cũng có thể sử dụng được cách gộp hai điều kiện đồng thời trong một công thức hàm SUMPRODUCT, công thức này sẽ là:

=SUMPRODUCT((C2:C18=”Cam”)*(MONTH(A2:A18)=6)*1) 

CHÚ Ý: Điều kiện được viết trong dấu ngoặc đơn và phải liên kết với nhau bằng dấu "*"

Ngoài ra chúng ta cũng phải nhân thêm một giá trị 1 để kết luận được kết quả về dạng số để có thể tính tổng. Lí do phải làm theo bước này là bởi kết quả của điều kiện sẽ tra về theo kiểu TRUE/FALSE  chứ không phải bằng 1.

Kết quả cho ra sẽ như sau

Yêu cầu	Điều kiện	Kết quả
Đếm số Cam đã bán được trong tháng 6 =SUMPRODUCT((C2:C18=”Cam”)*(MONTH(A2:A18)=6)*1)	Tên hàng = Cam Ngày = Tháng 6	5

Các vùng tham chiếu như A2:A18 và C2:C18 có thể cố định lại bằng phím F4.

*Nếu kết quả hiện #N/A, bạn có thể tham khảo tại đây để tìm cách khắc phục

Xem thêm: Hướng dẫn đếm các điều kiện loại trừ với SUMPRODUCT

Ví dụ 2: Tìm số sản phẩm "Táo" đã bán được của ca 1 trong tháng 6.

Ở ví dụ này ta sẽ có 3 tác vụ sẽ phải xử lý: sản phẩm "Táo", ca 1, tháng 6.

Tăng thêm điều kiện thì đồng nghĩa với việc phải tăng thêm thành phần trong hàm SUMPRODUCT:

=SUMPRODUCT(($B$2:$B$18=1)*(MONTH($A$2:$A$18)=6)*($C$2:$C$18=”Táo”)*1) 

Kết quả sẽ cho ra như sau.

Yêu cầu	Điều kiện	Kết quả.
Số Cam bán được trong tháng 6 =SUMPRODUCT(($C$2:$C18="Cam")*(MONTH($A$2:$A$18)=6)*1)	Tên hàng = Cam Ngày = Tháng 6	5
Số Táo mà ca 1 bán được trong tháng 6 =SUMPRODUCT(($B$2:$B$18=1)*(MONTH($A$2:$A$18)=6)*($C$2:$C$18="Táo")*1)	Ca = 1 Tên hàng = Táo  Ngày = tháng 6	1

Như vậy ta thấy rằng hàm SUMPRODUCT chỉ dài lên khi có thêm nhiều điều kiện yêu cầu nhưng về kết quả vẫn giữ nguyên. 

Xem thêm: Cách dùng hàm SUMPRODUCT để tổng hợp dữ liệu cho báo cáo

Tổng kết

Hi vọng bạn đọc qua bài viết này có thể hiểu hơn về hàm SUMPRODUCT trong việc tính tổng nhiều điều kiện. Để hiểu thêm về hàm SUMPRODUCT cũng như các tính năng của nó, bạn đọc hãy tham khảo những bài trên Blog của Gitiho nhé!