Bên cạnh hàm COUNIFS thuộc hàm điều kiện thì hàm SUMPRODUCT cũng là một lựa chọn tốt cho bạn khi thao tác với Excel. Bài viết hôm nay chúng ta sẽ đi tìm hiểu kĩ hơn về hàm SUMPRODUCT tính tổng nhiều điều kiện trong Excel nhé!
Nội dung bài viết
Nguyên tắc đếm của hàm SUMPRODUCT
Hàm SUMPRODUCT được biết đến là một hàm xử lý dữ liệu dạng mảng. Cấu trúc của hàm SUMPRODUCT đơn giản chỉ là những mảng:
=SUMPRODUCT(array1, [array2], [array3], …)
Nếu bạn chỉ nhìn vào cấu trúc trên thì liệu có hình dung được hàm SUMPRODUCT có tác dụng hay ho như thế nào không? Sức mạnh nằm ở chữ “”Mảng-Array”, bởi vì:
- Phạm vi của mảng rất linh hoạt, bao gồm nhiều vùng dữ liệu được kết nối lại hoặc là một phần trong từng phần tử của 1 vùng dữ liệu.
- Việc tính toán theo mảng hầu như không gặp giới hạn gì
Vậy thì hàm SUMPRODUCT đã thực hiện việc đếm dựa vào nguyên tắc nào?
Bản chất của việc đếm là từng phần tử nằm trong 1 vùng thỏa mãn điều kiện thì sẽ được tính là 1. Kết quả cần đếm sẽ là tổng của những giá trị thỏa mãn điều kiện đó. Vậy nên việc đếm theo điều kiện có thể hiểu gần giống như việc tính tổng các giá trị thỏa mãn điều kiện, chỉ khác ở chỗ là mỗi giá trị thỏa mãn này sẽ không phụ thuộc 1 giá trị nào mà chỉ có giá trị là 1.
Ứng dụng hàm SUMPRODUCT để đếm theo nhiều điều kiện
Dưới đây là một vài ví dụ để giúp bạn làm quen với việc sử dụng hàm SUMPRODUCT để đếm:
Tại yêu cầu này sẽ có 2 điều kiện đếm:
- Mặt hàng là Cam. Xét tại cột Tên hàng với điều kiện “Cam”
- Tháng 6. Xét tại cột Ngày, mỗi phần tử (ô) trong cột ngày sẽ chỉ xét giá trị tháng bằng 6
Như vậy thì mỗi giá trị khi thỏa mãn cả 2 điều kiện này thì sẽ được tính là 1. Kết quả thu được là tổng những giá trị thỏa mãn cả 2 điều kiện này.
Xét điều kiện thứ 1:
Theo như cách viết thông thường thì bạn sẽ viết:
C2=”Cam”
C3=”Cam”
…
C18=”Cam”
Khi viết dưới đạng mảng thì bạn có thể viết gọn là C2:C18=”Cam”. Khi viết như trên thì bạn hiểu là xét từng giá trị trong vùng C2:C18, ô nào có giá trị là Cam.
Xem thêm: Hướng dẫn cách sử dụng hàm INDIRECT trong Excel
Xét điều kiện thứ 2:
Để có thể xét tháng của một Ngày thì bạn dùng hàm MONTH. Để xét từng giá trị trong cột Ngày có tháng bằng 6 thì bạn có thể viết như sau:
MONTH(A2:A18)=6
Hàm MONTH thường chỉ xét được trên 1 giá trị. Do vậy, nếu viết thông thường thì sẽ chỉ viết dạng:
Month(A2)=6
Month(A3)=6
…
Month(A18)=6
Khi thực hiện viết dưới dạng công thức mảng thì bạn cũng có thể viết Month(A2:A18)=6
Bởi vì hàm Sumproduct là hàm dạng mảng nên bạn có thể dùng trực tiếp cách viết rút gọn này.
Hai điều kiện trên là đồng thời, nên trong hàm SUMPRODUCT bạn có thể viết công thức như sau:
=SUMPRODUCT((C2:C18=”Cam”)*(MONTH(A2:A18)=6)*1)
Mỗi điều kiện sẽ được viết trong dấu ngoặc đơn, và liên kết với nhau bởi dấu *.
Kết quả của những điều kiện sẽ trả về TRUE hoặc là FALSE chứ không phải bằng 1. Do vậy, phải tiến hành nhân thêm giá trị 1 để quy kết quả sang dạng số để có thể thực hiện tính tổng được.
Kết quả lúc này sẽ hiển thị như sau:
(Những vùng tham chiếu A2:A18, C2:C18 có thể được cố định lại bởi phím F4)
Yêu cầu 2: Tiến hành đếm số lần bán của ca 1 trong tháng 6 với mặt hàng Táo
Ở yêu cầu này sẽ có tới 3 điều kiện: Ca1, Tháng 6 và Mặt hàng “Táo”
Khi tiến hành tăng thêm điều kiện thì bạn chỉ cần tăng thêm những thành phần trong hàm SUMPRODUCT như bên dưới:
=SUMPRODUCT(($B$2:$B$18=1)*(MONTH($A$2:$A$18)=6)*($C$2:$C$18=”Táo”)*1)
Kết quả nhận được sẽ là:
Như vậy thì việc thêm điều kiện thực chất chỉ làm hàm SUMPRODUCT trở nên dài hơn mà thôi, trong khi đó ý nghĩa và độ khó không hề tăng lên. Khi bạn đã hiểu rõ phương pháp cũng như cách vận dụng hàm SUMPRODUCT vào việc đếm theo nhiều điều kiện thì bạn sẽ thấy được tính ứng dụng của việc này là rất cao trong thực tế. Hi vọng bài viết về cách sử dụng hàm SUMPRODUCT tính tổng nhiều điều kiện trong Excel sẽ giúp ích được bạn trong công việc và học tập. Chúc bạn thực hiện thành công!
Xem thêm: Hướng dẫn sử dụng hàm ISNA trong Excel
