Công thức chung
=SUMPRODUCT(COUNTIF(range1,range2))
Tóm lược
Để so sánh hai phạm vi và đếm tổng số kết quả phù hợp giữa hai phạm vi, bạn có thể sử dụng công thức kết hợp hàm COUNTIF với hàm SUMPRODUCT. Trong ví dụ được hiển thị, công thức trong ô G7 là:
=SUMPRODUCT(COUNTIF(B5:B11,D5:D11))
Lưu ý: công thức này không quan tâm đến vị trí hoặc thứ tự của các mục trong mỗi phạm vi.
Giải trình
Giả sử bạn có một danh sách "chính" của một số loại, và cũng có một danh sách khác chứa một số mục tương tự. Bạn muốn một công thức so sánh các giá trị trong danh sách thứ 2 để xem có bao nhiêu giá trị trong số chúng xuất hiện trong danh sách đầu tiên. Bạn không quan tâm đến thứ tự của các mục, bạn chỉ muốn biết có bao nhiêu mục trong danh sách 2 xuất hiện trong danh sách 1.
Hàm COUNTIF sẽ đếm những thứ trong một phạm vi đáp ứng tiêu chí của bạn. Thông thường, bạn sẽ cung cấp cho COUNTIF một phạm vi như A1: A10 và một tiêu chí đơn giản như "> 10". Sau đó, COUNTIF sẽ trả về số lượng ô trong A1: A10 lớn hơn 10.
Tuy nhiên, trong trường hợp này, chúng tôi đang cung cấp COUNTIF một phạm vi cho tiêu chí. Chúng tôi không sử dụng bất kỳ toán tử logic nào, có nghĩa là COUNTIF sẽ kiểm tra tính tương đương (tức là nó hoạt động như thể chúng tôi đã sử dụng toán tử bằng (=)).
Bởi vì chúng tôi cung cấp cho COUNTIF một phạm vi (còn được gọi là "mảng") chứa 7 mục, kết quả là COUNTIF sẽ trả về một mảng gồm 7 mục. Mỗi mục trong mảng kết quả đại diện cho một số lượng. Trong ví dụ, mảng mà COUNTIF trả về trông giống như sau:
(1; 1; 0; 1; 1; 0; 0)
Bây giờ chúng ta chỉ cần thêm các mục trong mảng này, đây là một công việc hoàn hảo cho SUMPRODUCT. Hàm SUMPRODUCT là một hàm đa năng xử lý mảng nguyên bản mà không cần bất kỳ cú pháp mảng đặc biệt nào.
Nếu bạn cung cấp SUMPRODUCT hai hoặc nhiều mảng, nó sẽ gộp nhiều mảng lại với nhau, tổng hợp kết quả và trả về số đó. Trong trường hợp này, chúng tôi cung cấp SUMPRODUCT chỉ một mảng, vì vậy nó chỉ đơn giản là tính tổng các mục trong mảng và trả về kết quả là 4.
Đối sánh giữa các hàng
Nếu bạn muốn so sánh hai phạm vi hoặc cột và muốn đếm kết quả phù hợp ở cấp hàng (nghĩa là chỉ đếm kết quả phù hợp khi cùng một mục xuất hiện ở cùng một vị trí), bạn sẽ cần một công thức khác.
