Trong bài này, chúng ta sẽ học cách SUM IF bằng cách sử dụng các giá trị tra cứu trong Excel.

Nói một cách dễ hiểu, trong khi làm việc với các bảng dữ liệu khác nhau, đôi khi chúng ta cần tính toán các giá trị trong các giá trị dựa trên bảng đầu tiên trong một bảng khác.

Điều kiện là lấy tổng các giá trị trong bảng đầu tiên bằng cách sử dụng các giá trị của bảng thứ hai. Đối với bài viết này, chúng ta sẽ cần sử dụng các hàm sau:

Tổng = (A1 B1 C1 .. + A2 B2 C2 .. +…)

Cú pháp:

= SUMPRODUCT ( array1 , [array2] , ...)

mảng: Là phạm vi hoặc danh sách các giá trị.

hàm trong Excel trả về SUM của các số đáp ứng các tiêu chí được chỉ định. Phạm vi SUM & phạm vi tiêu chí có thể giống hoặc khác nhau.

Công thức SUMIF chung trong Excel:

= SUMIF ( criteria_range , criteria , sum_range)

tiêu chí_ dải ô: phạm vi áp dụng điều kiện.

tiêu chí: điều kiện để áp dụng trên dải tiêu chí.

sum_range: phạm vi tổng trên cơ sở tiêu chí.

Bây giờ chúng ta sẽ tạo công thức cho các hàm này. Ở đây chúng tôi sẽ cung cấp dữ liệu và chúng tôi cần kết quả tổng hợp trong đó giá trị khớp với giá trị trong bảng tra cứu.

Công thức chung:

= SUMPRODUCT ( SUMIF ( result, records, sum_nums))

kết quả: các giá trị được so khớp trong bản ghi bảng tra cứu: các giá trị trong bảng đầu tiên sum_nums: các giá trị được tính tổng theo giá trị khớp.

Hãy kiểm tra công thức này bằng cách chạy nó trên một ví dụ Ở đây chúng tôi đang tính tổng điểm của một đội chơi bóng đá bằng cách sử dụng bảng điểm hoặc bảng tra cứu.

image

Ở đây kết quả (B8: B10) được cung cấp dưới dạng phạm vi được đặt tên & điểm (C8: C10) được đưa ra là phạm vi đã đặt tên.

Bây giờ chúng ta sẽ sử dụng công thức dưới đây để lấy SUM của điểm cho Tháng 1 trong ô F3 Sử dụng công thức:

= SUMPRODUCT ( SUMIF ( result, B3:E3, points))

Giải thích:

  • Hàm SUMIF khớp kết quả với các giá trị trong B3: E3.

  • Sau đó các điểm tương ứng với kết quả được trả về hàm SUMPRODUCT.

  • Hàm SUMPRODUCT trả về SUM của các giá trị được cung cấp dưới dạng mảng cho hàm.

image

Như bạn có thể thấy ở đây, tổng số điểm mà nhóm ghi được trong tháng đầu tiên là 7.

3 (W) + 0 (L) + 1 (D) + 3 (W) = 7 điểm

Sao chép công thức trong các ô khác, chọn các ô lấy ô đầu tiên nơi công thức đã được áp dụng, sử dụng phím tắt Ctrl + D hoặc sử dụng tùy chọn kéo xuống ô trong excel.

image

Như bạn có thể thấy trong ảnh chụp nhanh ở trên, số điểm ghi được trong 4 tháng khác nhau.

Nhận SUM của tất cả các điểm trong 4 tháng.

image

Điều đó có nghĩa là đội đã ghi được tổng cộng 18 điểm trong 4 tháng.

Phương pháp thay thế

Sử dụng phương thức SUM và mảng thay vì sử dụng hàm SUMPRODUCT.

Sử dụng công thức:

{ = SUM ( SUMIF ( result , B3:E3 , points )) }

Dấu ngoặc nhọn là phương thức mảng đề xuất hàm tính toán tất cả giá trị trả về bởi hàm SUMIF.

Lưu ý: Không sử dụng các ký hiệu dấu ngoặc nhọn theo cách thủ công. Sử dụng Ctrl Shift + Enter để sử dụng phương thức mảng trong Excel.

image

Như bạn có thể thấy từ công thức trên, bạn có thể nhận được tổng điểm bằng cách sử dụng bảng tra cứu có điều kiện. Ghi chú:

  1. Công thức chỉ hoạt động với số.

  2. Công thức chỉ hoạt động khi không có bản sao trong bảng tra cứu. Hàm SUMPRODUCT coi các giá trị không phải là số là 0.

  3. Hàm SUMPRODUCT coi giá trị logic là TRUE là 1 và False là 0.

  4. Mảng đối số phải có cùng độ dài với hàm khác Hy vọng bài viết này về cách Chỉ trả về SUM từ các công thức trong Excel là giải thích. Tìm thêm các bài viết về hàm SUMPRODUCT tại đây. Vui lòng chia sẻ câu hỏi của bạn bên dưới trong hộp bình luận. Chúng tôi sẽ hỗ trợ bạn.

Các bài viết liên quan

Bài viết phổ biến

liên kết: / logic-công thức-nếu-hàm-với-ký tự đại diện [Nếu có ký tự đại diện]