Công thức chung
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Tóm lược
Để tính tổng các giá trị trong các cột và hàng phù hợp, bạn có thể sử dụng hàm SUMPRODUCT. Trong ví dụ được hiển thị, công thức trong J6 là:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
trong đó dữ liệu (C5: G14), ngày (B5: B14) và mã (C4: G4) là các phạm vi được đặt tên.
Giải trình
Hàm SUMPRODUCT có thể xử lý các mảng nguyên bản mà không yêu cầu nhập chuyển điều khiển.
Trong trường hợp này, chúng tôi đang nhân tất cả các giá trị trong dữ liệu phạm vi đã đặt tên với hai biểu thức để lọc ra các giá trị không quan tâm. Biểu thức đầu tiên áp dụng bộ lọc dựa trên các mã:
(codes=J4)
Vì J4 chứa "A002", biểu thức tạo ra một mảng các giá trị TRUE FALSE như sau:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Biểu thức thứ hai lọc vào ngày:
(days=J5)
Vì J4 chứa "Wed", biểu thức tạo ra một mảng các giá trị TRUE FALSE như sau:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Trong Excel, các giá trị TRUE FALSE được tự động ép buộc thành các giá trị 1 và 0 bởi bất kỳ phép toán nào, do đó, phép toán nhân sẽ ép các mảng ở trên thành các mảng một và các số không, đồng thời tạo ra một mảng 2D có cùng kích thước với dữ liệu gốc. Quá trình này có thể được hình dung như hình dưới đây:
Cuối cùng, hàm SUMPRODUCT trả về tổng của tất cả các phần tử trong mảng cuối cùng, 9.
Đếm thay vì tổng
Nếu bạn muốn đếm các giá trị phù hợp thay vì tính tổng, bạn có thể rút ngắn công thức thành:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Lưu ý rằng số lượng này sẽ bao gồm các ô trống.
Ghi chú
- Mặc dù ví dụ chỉ hiển thị một cột phù hợp, công thức này sẽ tính tổng chính xác nhiều cột phù hợp.
- Nếu bạn chỉ cần đối sánh các cột (không phải hàng), bạn có thể sử dụng công thức như thế này.
- Để chỉ đối sánh các hàng, bạn có thể sử dụng hàm COUNTIFS.
