Công thức Excel: Tính tổng các cột và hàng phù hợp
Công thức Excel: Tính tổng các cột và hàng phù hợp

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))

Ở đâu dữ liệu (C5: G14), ngày (B5: B14), và mã số (C4: G4) là phạm vi được đặt tên.

Giải trình

Hàm SUMPRODUCT có thể xử lý mảng nguyên bản, không yêu cầu nhập thay đổi đ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 được 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 giá trị đơn và 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:

Phép nhân mảng bên trong SUMPRODUCT

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ú

  1. 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.
  2. 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.
  3. Để chỉ đối sánh các hàng, bạn có thể sử dụng Hàm COUNTIFS.

https://exceljet.net/formula/sum-matching-columns-and-rows