Hàm SUMIF là một hàm toán học hữu ích trong Excel, giúp bạn tính tổng các ô thỏa mãn điều kiện cụ thể. Tuy nhiên, trong nhiều trường hợp, việc chỉ sử dụng hàm SUMIF cơ bản có thể không đáp ứng đủ nhu cầu xử lý dữ liệu phức tạp. Trong bài viết này, HỌC TIN sẽ hướng dẫn bạn cách sử dụng hàm SUMIF nâng cao, đặc biệt là khi làm việc với nhiều điều kiện dạng OR, giúp bạn tối ưu hóa quy trình tính toán và phân tích dữ liệu hiệu quả hơn.
Kết hợp SUMIF và SUMIF để tính tổng các ô thỏa mãn một trong hai điều kiện
Nếu bạn muốn tính tổng các số trong một cột dựa trên các điều kiện cụ thể, một cách tiếp cận đơn giản là sử dụng hàm SUMIF cho từng điều kiện riêng lẻ rồi cộng các kết quả lại.
Công thức cơ bản như sau: =SUMIF(range, criteria1, sum_range) + SUMIF(range, criteria2, sum_range)
Ví dụ: Trong bảng dữ liệu dưới đây, nếu bạn muốn tính tổng doanh số cho hai sản phẩm Apple và Lemon, bạn có thể sử dụng công thức sau: =SUMIF(A2:A10, “apple”, B2:B10) + SUMIF(A2:A10, “lemon”, B2:B10)
Hoặc, bạn có thể nhập các điều kiện vào các ô riêng biệt và tham chiếu chúng trong công thức: =SUMIF(A2:A10, E1, B2:B10) + SUMIF(A2:A10, E2, B2:B10)
Trong đó:
- A2:A10: Phạm vi chứa tên sản phẩm.
- B2:B10: Cột chứa doanh số cần tính tổng.
- E1 và E2: Ô chứa các điều kiện (cụ thể là Apple và Lemon).
Công thức trên hoạt động như sau: Hàm SUMIF đầu tiên sẽ tính tổng doanh số cho Apple, và hàm SUMIF thứ hai tính tổng doanh số cho Lemon. Kết quả cuối cùng là tổng doanh số của cả hai sản phẩm.
Sử dụng SUMIF kết hợp với mảng hằng số – Công thức đơn giản với nhiều tiêu chí
Khi làm việc với nhiều hơn hai điều kiện, cách kết hợp SUMIF + SUMIF có thể trở nên phức tạp và khó đọc. Thay vào đó, bạn có thể sử dụng hàm SUMIF nâng cao kết hợp với hằng số mảng để tạo ra công thức ngắn gọn nhưng vẫn hiệu quả.
Công thức tổng quát: =SUM(SUMIF(range, {criteria1, criteria2, criteria3, …}, sum_range))
Công thức này hoạt động dựa trên logic OR, nghĩa là một ô sẽ được tính tổng nếu bất kỳ điều kiện nào trong mảng được đáp ứng.
Ví dụ: Trong bảng dữ liệu dưới đây, để tính tổng doanh số của ba sản phẩm Apple, Lemon và Orange, bạn có thể viết: =SUM(SUMIF(A2:A10, {“Apple”,”Lemon”,”Orange”}, B2:B10))
Trong trường hợp bạn muốn linh hoạt hơn, các tiêu chí có thể được nhập vào một phạm vi ô (ví dụ: E1:E3) và tham chiếu trực tiếp trong công thức như sau: =SUM(SUMIF(A2:A10, E1:E3, B2:B10))
Trong đó:
- A2:A10: Phạm vi chứa tên sản phẩm.
- B2:B10: Phạm vi chứa các giá trị cần tính tổng.
- E1:E3: Ô chứa các điều kiện.
Trong Excel 365, công thức này hoạt động như bình thường và chỉ cần nhấn ENTER để hoàn thành. Tuy nhiên, trong các phiên bản cũ hơn như Excel 2019 hoặc trước đó, bạn cần nhấn tổ hợp phím CTRL + SHIFT + ENTER để kích hoạt công thức mảng.
Cách hoạt động của công thức như sau: Hằng số mảng trong công thức sẽ tạo ra một mảng kết quả tương ứng với từng điều kiện (cụ thể là doanh số cho Apple, Lemon, Orange). Kết quả cuối cùng được tính bằng hàm SUM để cho ra tổng số. Đây là cách sử dụng hiệu quả hàm SUMIF nâng cao để đơn giản hóa việc xử lý nhiều điều kiện.
Xem thêm: Cách Dùng Hàm SUMIF Trong Excel Để Tính Tổng Có Điều Kiện
Kết hợp SUMPRODUCT và SUMIF để tính tổng các ô thỏa mãn nhiều điều kiện OR
Nếu bạn muốn tính tổng với nhiều điều kiện dạng OR mà không cần sử dụng công thức mảng, cách kết hợp hàm SUMIF nâng cao với SUMPRODUCT là một giải pháp đơn giản và hiệu quả. Thay vì dùng SUM để cộng các kết quả, SUMPRODUCT xử lý trực tiếp các mảng và cho phép công thức hoạt động bình thường mà không cần tổ hợp phím đặc biệt.
Công thức tổng quát: =SUMPRODUCT(SUMIF(range, criteria_range, sum_range))
Ví dụ: Nếu các điều kiện được liệt kê trong các ô E1, E2 và E3, bạn có thể viết công thức như sau: =SUMPRODUCT(SUMIF(A2:A10, E1:E3, B2:B10))
Trong đó:
- A2:A10: Cột chứa các mục cần kiểm tra.
- B2:B10: Cột chứa các giá trị cần tính tổng.
- E1:E3: Phạm vi ô chứa các điều kiện.
Cách hoạt động của công thức
- Hàm SUMIF sẽ tính tổng cho từng điều kiện được cung cấp trong E1:E3, tạo ra một mảng kết quả đại diện cho tổng của mỗi điều kiện.
- SUMPRODUCT sẽ cộng các giá trị trong mảng này lại và xuất ra kết quả cuối cùng.
Không giống với công thức mảng sử dụng SUM, hàm SUMPRODUCT được thiết kế để xử lý mảng trực tiếp. Vì vậy, bạn chỉ cần nhấn ENTER để hoàn tất công thức, kể cả trong các phiên bản Excel cũ hơn.
Như vậy, sử dụng kết hợp SUMIF và SUMPRODUCT là một cách hiệu quả để khai thác sức mạnh của hàm SUMIF nâng cao, giúp bạn xử lý nhiều điều kiện mà không cần phải viết các công thức phức tạp hoặc sử dụng hằng số mảng.
Sử dụng hàm SUMIF nâng cao với nhiều tiêu chí và ký tự đại diện
Một trong những tính năng mạnh mẽ của hàm SUMIF nâng cao là khả năng sử dụng ký tự đại diện để đáp ứng nhiều tiêu chí cùng lúc. Điều này đặc biệt hữu ích khi bạn cần tính tổng các giá trị liên quan đến một nhóm dữ liệu có tên tương tự hoặc chứa các mẫu đặc biệt.
Ví dụ: Nếu bạn muốn tính tổng doanh số bán hàng cho tất cả các loại sản phẩm có tên chứa Apples và Bananas, bạn có thể sử dụng công thức sau: =SUM(SUMIF(A2:A10, {“*Apples”,”*Bananas”}, B2:B10))
Trong đó:
- A2:A10: Cột chứa danh sách các sản phẩm.
- B2:B10: Cột chứa doanh số cần tính tổng.
- {“*Apples”,”*Bananas”}: Mảng các tiêu chí, sử dụng ký tự đại diện * để khớp với mọi chuỗi ký tự đứng trước từ khóa.
Nếu các điều kiện được nhập vào các ô cụ thể, bạn cũng có thể tham chiếu phạm vi chứa các ký tự đại diện, chẳng hạn: =SUMPRODUCT(SUMIF(A2:A10, E1:E2, B2:B10)). Trong đó, các ô E1 và E2 chứa các tiêu chí như *Apples hoặc *Bananas.
Cách hoạt động của ký tự đại diện:
- Dấu hoa thị * đại diện cho bất kỳ chuỗi ký tự nào, chẳng hạn *Apples sẽ khớp với cả Green Apples và Red Apples.
- Nếu bạn muốn khớp chuỗi ký tự bất kỳ ở cả hai bên, sử dụng dạng *keyword*, ví dụ: *apple* sẽ tìm tất cả các mục có từ “apple” ở bất kỳ vị trí nào.
Như vậy, bằng cách sử dụng ký tự đại diện linh hoạt, bạn có thể tận dụng hàm SUMIF nâng cao để nhanh chóng tổng hợp dữ liệu từ các nhóm điều kiện phức tạp mà không cần viết công thức dài dòng.
Tổng kết lại, hàm SUMIF nâng cao là một công cụ mạnh mẽ giúp bạn xử lý các bài toán tính tổng trong Excel một cách linh hoạt và hiệu quả, đặc biệt khi làm việc với nhiều điều kiện phức tạp. Qua các phương pháp như kết hợp với SUMPRODUCT, sử dụng mảng hằng số hay áp dụng ký tự đại diện, bạn có thể tối ưu hóa công việc và tiết kiệm thời gian đáng kể. Hy vọng bài viết đã giúp bạn nắm vững cách sử dụng hàm này để giải quyết các yêu cầu tính toán một cách chuyên nghiệp.
Xem thêm: Hàm IF Kết Hợp VLOOKUP Trong Excel Dò Giá Trị Theo Điều Kiện
