Nhắc tới đếm theo nhiều điều kiện chúng ta thường nghĩ tới hàm COUNTIFS. Nhưng bạn có biết rằng SUMPRODUCT cũng làm được điều này? Hãy cùng Học Excel Online tìm hiểu cách dùng hàm SUMPRODUCT để đếm theo nhiều điều kiện nhé:

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

Hàm SUMPRODUCT là một hàm xử lý dữ liệu dạng mảng. Cấu trúc của SUMPRODUCT đơn giản chỉ là các mảng:

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

Nếu chỉ nhìn vào cấu trúc trên thì bạn có hình dung được hàm SUMPRODUCT có sức mạ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, có thể gồm nhiều vùng dữ liệu được kết nối lại hoặc một phần trong mỗi phần tử của 1 vùng dữ liệu.
• Việc tính toán theo mảng gần như không gặp giới hạn gì

Vậy hàm SUMPRODUCT thực hiện việc đếm dựa trên nguyên tắc nào?

Bản chất của việc đếm là mỗi phần tử trong 1 vùng thỏa mãn điều kiện thì được tính là 1. Kết quả cần đếm là tổng của các giá trị thỏa mãn điều kiện đó. Như vậy trong việc đếm theo điều kiện có thể hiểu gần giống tính tổng các giá trị thỏa mãn điều kiện, chỉ khác là mỗi giá trị thỏa mãn này 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 số ví dụ để giúp chúng ta làm quen với việc dùng hàm SUMPRODUCT để đếm:

Yêu cầu thứ 1: Dựa vào bảng dữ liệu A1:D18 đếm số mặt hàng Cam bán trong tháng 6

Trong yêu cầu này có 2 điều kiện đếm:

• Mặt hàng là Cam. Xét trong cột Tên hàng với điều kiện “Cam”
• Tháng 6. Xét trong cột Ngày, mỗi phần tử (ô) trong cột ngày chỉ xét giá trị tháng bằng 6

Như vậy mỗi giá trị thỏa mãn cả 2 điều kiện này thì được tính là 1. Kết quả là tổng các giá trị thỏa mãn cả 2 điều kiện này.

Xét điều kiện thứ 1:

Theo cách viết thông thường thì chúng ta viết

C2=”Cam”

C3=”Cam”

…

C18=”Cam”

Khi viết dưới đạng mảng thì chúng ta có thể viết gọn là C2:C18=”Cam”. Khi viết như trên thì chúng ta hiểu là xét mỗi giá trị trong vùng C2:C18, ô nào có giá trị là Cam.

Xét điều kiện thứ 2:

Để xét tháng của một Ngày thì chúng ta sử dụng hàm MONTH. Để xét mỗi giá trị trong cột Ngày có tháng bằng 6 thì chúng ta có thể viết như sau:

MONTH(A2:A18)=6

Hàm MONTH thông thường chỉ xét được trên 1 giá trị. Do đó nếu viết thông thường thì chỉ viết dạng:

Month(A2)=6

Month(A3)=6

…

Month(A18)=6

Khi viết dưới dạng công thức mảng thì có thể viết Month(A2:A18)=6

Do hàm Sumproduct là hàm dạng mảng nên chúng ta có thể sử 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 có thể viết như sau:

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

Mỗi điều kiện được viết trong dấu ngoặc đơn, liên kết với nhau bởi dấu *.

Kết quả của các điều kiện sẽ trả về TRUE hoặc FALSE chứ không phải bằng 1. Do đó phải nhân thêm giá trị 1 để quy kết quả về dạng số để có thể tính tổng được.

Kết quả như sau:

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

Yêu cầu 2: Đếm số lần bán của ca 1 trong tháng 6 với mặt hàng Táo

Trong yêu cầu này có tới 3 điều kiện: Ca1, Tháng 6 và Mặt hàng “Táo”

Khi tăng thêm điều kiện thì chúng ta chỉ cần tăng thêm các thành phần trong hàm SUMPRODUCT như sau:

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

Kết quả:

Như vậy việc thêm điều kiện thực ra chỉ làm hàm SUMPRODUCT dài hơn, còn ý nghĩa và độ khó không hề tăng lên. Khi đã hiểu rõ phương pháp và cách vận dụng hàm SUMPRODUCT vào việc đếm theo nhiều điều kiện thì chúng ta sẽ thấy tính ứng dụng của việc này rất cao trong thực tế.

Xem thêm:

4 phương pháp đếm nhiều điều kiện dạng hoặc – Hàm SUMPRODUCT

Ngoài ra Học Excel Online xin giới thiệu với các bạn Khóa học Excel từ cơ bản tới nâng cao dành cho người đi làm. Đây là 1 khóa học rất đầy đủ kiến thức và bổ trợ rất tuyệt vời cho bạn trong việc làm quen với Excel, sử dụng các công cụ, các hàm trong Excel để giúp bạn làm việc trên Excel được tốt hơn, hiệu quả hơn. Chi tiết xem tại: