Công thức chung
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Tóm lược
Để chỉ đếm các hàng hiển thị với tiêu chí, bạn có thể sử dụng một công thức khá phức tạp dựa trên SUMPRODUCT, SUBTOTAL và OFFSET. Trong ví dụ được hiển thị, công thức trong C12 là:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Lời nói đầu
Hàm SUBTOTAL có thể dễ dàng tạo tổng và đếm cho các hàng ẩn và không ẩn. Tuy nhiên, nó không thể xử lý các tiêu chí như COUNTIF hoặc SUMIF mà không có một số trợ giúp. Một giải pháp là sử dụng SUMPRODUCT để áp dụng cả hàm SUBTOTAL (thông qua OFFSET) và tiêu chí. Chi tiết của cách tiếp cận này được mô tả dưới đây.
Giải trình
Về cốt lõi, công thức này hoạt động bằng cách thiết lập hai mảng bên trong SUMPRODUCT. Mảng đầu tiên áp dụng tiêu chí và mảng thứ hai xử lý khả năng hiển thị:
=SUMPRODUCT(criteria*visibility)
Tiêu chí được áp dụng với một phần của công thức:
=(C5:C8=C10)
Tạo một mảng như thế này:
(FALSE;TRUE;FALSE;TRUE)
Trường hợp TRUE có nghĩa là "đáp ứng tiêu chí". Lưu ý vì chúng ta đang sử dụng phép nhân (*) trên mảng này, các giá trị TRUE FALSE sẽ tự động được chuyển đổi thành 1 và 0 bằng phép toán, vì vậy chúng ta kết thúc bằng:
(0;1;0;1)
Bộ lọc hiển thị được áp dụng bằng SUBTOTAL, với hàm số 103.
SUBTOTAL có thể loại trừ các hàng ẩn khi chạy tính toán, vì vậy chúng tôi có thể sử dụng nó trong trường hợp này để tạo "bộ lọc" nhằm loại trừ các hàng ẩn bên trong SUMPRODUCT. Tuy nhiên, vấn đề là SUBTOTAL trả về một số duy nhất, trong khi chúng ta cần một mảng kết quả để sử dụng nó thành công bên trong SUMPRODUCT. Mẹo là sử dụng OFFSET để cung cấp SUBTOTAL một tham chiếu trên mỗi hàng, để OFFSET sẽ trả về một kết quả trên mỗi hàng.
Tất nhiên, điều đó đòi hỏi một thủ thuật khác, đó là cung cấp cho OFFSET một mảng chứa một số trên mỗi hàng, bắt đầu bằng số không. Chúng tôi làm điều đó với một biểu thức được xây dựng trên hàm ROW:
=ROW(C5:C8)-MIN(ROW(C5:C8)
sẽ tạo ra một mảng như thế này:
(0;1;2;3)
Tóm lại, mảng thứ hai (xử lý khả năng hiển thị bằng SUBTOTAL), được tạo như sau:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
Và, cuối cùng, chúng tôi có:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Trả về 1.
Nhiều tiêu chí
Bạn có thể mở rộng công thức để xử lý nhiều tiêu chí như sau:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Tổng hợp kết quả
Để trả về một tổng giá trị thay vì một số đếm, bạn có thể điều chỉnh công thức để bao gồm một phạm vi tổng:
=SUMPRODUCT(criteria*visibility*sumrange)
Các mảng tiêu chí và khả năng hiển thị hoạt động giống như đã giải thích ở trên, loại trừ các ô không hiển thị. Nếu bạn cần đối sánh từng phần, bạn có thể tạo biểu thức bằng cách sử dụng ISNUMBER + SEARCH, như được giải thích ở đây.
