Công thức chung
=SUMPRODUCT(SUMIF(codes,lookups,values))
Tóm lược
Để tính tổng các giá trị được truy xuất bằng thao tác tra cứu, bạn có thể sử dụng hàm SUMPRODUCT với hàm SUMIF.
Trong ví dụ được hiển thị, công thức trong H5 là:
=SUMPRODUCT(SUMIF(codes,C5:G5,values))
Trong đó các mã là dải ô được đặt tên J4: J5 và các giá trị là dải ô được đặt tên K4: K5.
Bối cảnh
Đôi khi bạn có thể muốn tính tổng nhiều giá trị được truy xuất bằng thao tác tra cứu. Trong ví dụ này, chúng tôi muốn tính tổng thời gian nghỉ lễ được thực hiện mỗi tuần dựa trên hệ thống mã, trong đó F = cả ngày và H = nửa ngày. Nếu một ngày trống, không có thời gian được lấy.
Thách thức là tìm một công thức vừa tra cứu vừa tính tổng các giá trị liên quan đến F và H.
Giải trình
Cốt lõi của công thức này là SUMIF, được sử dụng để tra cứu các giá trị chính xác cho F và H. Sử dụng SUMIF để tra cứu các giá trị là một kỹ thuật nâng cao hơn hoạt động tốt khi các giá trị là số và không có bản sao trong "bảng tra cứu ".
Mẹo trong trường hợp này là tiêu chí cho SUMIF không phải là một giá trị đơn lẻ, mà là một mảng giá trị trong phạm vi C5: G5:
=SUMPRODUCT(SUMIF(codes,C5:G5,values))
Bởi vì chúng tôi đang cung cấp SUMIF nhiều hơn một tiêu chí, SUMIF sẽ trả về nhiều hơn một kết quả. Trong ví dụ được hiển thị, kết quả của SUMIF là mảng sau:
(1,0.5,0,0,0)
Lưu ý rằng chúng tôi nhận được 1 cho mỗi "F" và 0,5 cho mỗi "H"., Và các giá trị trống trong tuần tạo ra bằng không.
Cuối cùng, chúng tôi sử dụng SUMPRODUCT để cộng các giá trị trong mảng được trả về bởi SUMIF. Bởi vì chỉ có một mảng duy nhất, hàm SUMPRODUCT chỉ trả về tổng của tất cả các giá trị.
