Công thức chung
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
Tóm lược
Để tính tổng có điều kiện các phạm vi giống hệt nhau tồn tại trong các trang tính riêng biệt, tất cả trong một công thức, bạn có thể sử dụng hàm SUMIF với INDIRECT, được bao bọc trong hàm SUMPRODUCT. Trong ví dụ được hiển thị, công thức trong C9 là:
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))
Giải trình
Dữ liệu trên mỗi trang trong số ba trang tính đang được xử lý trông giống như sau:
Trước hết, hãy lưu ý rằng bạn không thể sử dụng SUMIF với tham chiếu 3D "bình thường" như sau:
Sheet1:Sheet3!D4:D5
Đây là "cú pháp 3D" tiêu chuẩn nhưng nếu bạn cố gắng sử dụng nó với SUMIF, bạn sẽ gặp lỗi #VALUE. Vì vậy, để giải quyết vấn đề này, bạn có thể sử dụng phạm vi được đặt tên "trang tính" liệt kê từng trang tính (tab trang tính) mà bạn muốn bao gồm. Tuy nhiên, để xây dựng các tham chiếu mà Excel sẽ diễn giải chính xác, chúng ta cần nối tên trang tính với phạm vi mà chúng ta cần làm việc và sau đó sử dụng INDIRECT để Excel nhận ra chúng một cách chính xác.
Ngoài ra, vì phạm vi được đặt tên "trang tính" chứa nhiều giá trị (tức là một mảng), kết quả của SUMIF trong trường hợp này cũng là một mảng (đôi khi được gọi là "mảng kết quả). Vì vậy, chúng tôi sử dụng SUMPRODUCT để xử lý nó, vì SUMPRODUCT có khả năng xử lý mảng nguyên bản mà không yêu cầu Ctrl-Shift-Enter, giống như nhiều công thức mảng khác.
Cách khác
Ví dụ trên hơi phức tạp. Một cách khác để xử lý vấn đề này là thực hiện tính tổng có điều kiện "cục bộ" trên mỗi trang tính, sau đó sử dụng tổng 3D thông thường để cộng từng giá trị trên tab tóm tắt.
Để thực hiện việc này, hãy thêm công thức SUMIF vào mỗi trang tính sử dụng ô tiêu chí trên bảng tóm tắt. Sau đó, khi bạn thay đổi tiêu chí, tất cả các công thức SUMIF được liên kết sẽ cập nhật.
