Hàm Sumifs trong Excel: Cách tính tổng nhiều điều kiện và ví dụ

Hàm SUMIFS trong Excel giúp bạn tính tổng các giá trị dựa trên nhiều điều kiện khác nhau một cách nhanh chóng và chính xác. Cùng AntBook hướng dẫn bạn chi tiết cú pháp, cách sử dụng và những lưu ý quan trọng để tránh lỗi khi áp dụng hàm SUMIFS.

1. Hàm Sumifs trong Excel là gì? Dùng để làm gì?

Hàm SUMIFS là một trong những hàm quan trọng trong Excel, giúp tính tổng các giá trị trong một phạm vi dữ liệu dựa trên nhiều điều kiện khác nhau.

Nếu bạn chỉ cần tính tổng tất cả các giá trị trong một phạm vi nhất định, bạn có thể sử dụng hàm SUM. Trong trường hợp cần tính tổng các giá trị thỏa mãn một điều kiện cụ thể, hàm SUMIF sẽ là lựa chọn phù hợp.

Hàm Sumifs tính tổng các giá trị trong một phạm vi dữ liệu dựa trên nhiều điều kiện

Tuy nhiên, khi làm việc với bảng dữ liệu phức tạp, bạn có thể cần tính tổng dựa trên nhiều điều kiện đồng thời, chẳng hạn như tổng doanh thu của một sản phẩm cụ thể trong một khoảng thời gian nhất định hoặc tổng số lượng đơn hàng theo từng khu vực và loại sản phẩm. Lúc này, hàm SUMIFS sẽ giúp bạn thực hiện phép tính một cách chính xác và hiệu quả hơn.

2. Cú pháp của hàm Sumifs trong Excel là gì?

Hàm SUMIFS trong Excel dùng để tính tổng các giá trị trong một phạm vi, với điều kiện thỏa mãn nhiều tiêu chí khác nhau. Cú pháp của hàm như sau:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …)

Trong đó:

• sum_range: Phạm vi chứa các giá trị cần tính tổng. Các ô trống hoặc chứa văn bản sẽ tự động bị bỏ qua. Đây là tham số bắt buộc.
• criteria_range1: Phạm vi chứa dữ liệu cần kiểm tra theo điều kiện criteria1. Đây cũng là tham số bắt buộc.
• criteria1: Điều kiện áp dụng cho criteria_range1. Điều kiện này có thể là một số, biểu thức logic (>, <, =,…), một giá trị cụ thể hoặc một tham chiếu ô. Đây là tham số bắt buộc.
• criteria_range2, criteria2,…: Các phạm vi và điều kiện bổ sung (tùy chọn). Excel cho phép tối đa 127 cặp criteria_range và criteria, giúp người dùng linh hoạt hơn trong việc tính toán dữ liệu theo nhiều điều kiện khác nhau.

Cú pháp của hàm Sumifs nhiều điều kiện trong Excel

Hàm SUMIFS là công cụ mạnh mẽ giúp tổng hợp dữ liệu một cách có điều kiện, phù hợp với nhiều lĩnh vực như kế toán, tài chính, quản lý dữ liệu và báo cáo.

3. Ví dụ về cách dùng hàm Sumifs trong Excel

Để hiểu rõ hơn về cách sử dụng hàm SUMIFS, hãy cùng xem một ví dụ cụ thể.

Ví dụ 1: Cho một bảng dữ liệu bán hàng, yêu cầu tính tổng số tiền của sản phẩm “Thước” với điều kiện giá bán trên 30.000.

Các bước thực hiện:

Bước 1: Tại ô kết quả, nhập công thức sau:

=SUMIFS(D2:D14, B2:B14, “Thước”, D2:D14, “>30000”)

Trong đó:

• D2:D14 – sum_range: Phạm vi chứa giá trị cần tính tổng (cột số tiền).
• B2:B14 – criteria_range1: Phạm vi chứa tên sản phẩm.
• “Thước” – criteria1: Điều kiện lọc, chỉ tính tổng các dòng có sản phẩm là “Thước”.
• D2:D14 – criteria_range2: Phạm vi kiểm tra điều kiện giá tiền.
• “>30000” – criteria2: Chỉ tính những sản phẩm có giá trên 30.000.

Ví dụ về hàm Sumifs nhiều điều kiện trong Excel

Bước 2: Nhấn Enter, Excel sẽ tự động tính toán và hiển thị tổng số tiền của sản phẩm “Thước” với giá bán lớn hơn 30.000.

Hàm SUMIFS trong trường hợp này giúp bạn lọc và tính tổng dữ liệu theo nhiều điều kiện một cách nhanh chóng, tiết kiệm thời gian và đảm bảo độ chính xác cao.

Ví dụ về hàm Sumifs nhiều điều kiện trong Excel

Ví dụ 2: Cách sử dụng hàm SUMIFS để tính tổng giá tiền của tất cả trái cây, ngoại trừ trái Cam

Giả sử bạn có một bảng dữ liệu trong Excel về giá tiền của các loại trái cây, và bạn muốn tính tổng giá trị của tất cả các trái cây có giá trị lớn hơn 70, trừ đi trái Cam. Để thực hiện điều này, bạn có thể sử dụng hàm SUMIFS.

Các bước thực hiện:

Bước 1: Trong ô tham chiếu kết quả (có thể là ô E2, chẳng hạn), bạn nhập công thức sau:

=SUMIFS(D2:D7, C2:C7, “>70”, B2:B7, “<>Cam”)

Trong đó:

• SUMIFS: Đây là hàm tính tổng có điều kiện, cho phép bạn tính tổng của các giá trị thỏa mãn một hoặc nhiều điều kiện.
• D2:D7 (sum_range): Đây là phạm vi dữ liệu cần tính tổng, trong trường hợp này là giá tiền của các loại trái cây.
• C2:C7 (criteria_range1): Đây là phạm vi dữ liệu áp dụng điều kiện đầu tiên, ở đây là giá tiền của trái cây.
• “>70” (criteria1): Đây là điều kiện 1, yêu cầu các giá trị trong phạm vi C2:C7 phải lớn hơn 70.
• B2:B7 (criteria_range2): Đây là phạm vi dữ liệu áp dụng điều kiện thứ hai, trong trường hợp này là tên các loại trái cây.
• “<>Cam” (criteria2): Đây là điều kiện 2, yêu cầu loại trái cây không phải là “Cam” (ký hiệu <> có nghĩa là “không phải”).

Ví dụ về hàm Sumifs nhiều điều kiện trong Excel

Bước 2: Sau khi nhập công thức xong, nhấn phím Enter. Excel sẽ tự động tính tổng các giá trị từ phạm vi D2:D7 mà thỏa mãn điều kiện là giá trị lớn hơn 70 và loại trái cây không phải là Cam. Kết quả sẽ được hiển thị ngay tại ô bạn đã chọn.

Ví dụ về hàm Sumifs nhiều điều kiện trong Excel

Hàm SUMIFS là công cụ rất mạnh trong Excel để tính tổng có điều kiện, giúp bạn dễ dàng xử lý các bài toán phức tạp với nhiều tiêu chí lựa chọn.

4. Lưu ý: 0 (Không) được hiển thị thay vì kết quả mong muốn

Khi sử dụng hàm SUMIFS trong Excel, nếu bạn không nhập điều kiện đúng hoặc không sử dụng dấu ngoặc kép một cách chính xác trong các tham số điều kiện (criteria), Excel có thể hiển thị kết quả là 0 (không), thay vì trả về kết quả như bạn mong đợi. Để tránh gặp phải vấn đề này và đảm bảo rằng công thức hoạt động chính xác, bạn cần phải chú ý cách sử dụng dấu ngoặc kép “” khi nhập các điều kiện.

Cách khắc phục lỗi này:

Khi nhập các điều kiện trong hàm SUMIFS, bạn cần chắc chắn rằng tất cả các điều kiện cần phải có dấu ngoặc kép bao quanh các giá trị văn bản hoặc điều kiện trống. Cụ thể, nếu bạn đang so sánh giá trị ô dữ liệu với một điều kiện trống hoặc một chuỗi rỗng, bạn phải sử dụng “” để biểu thị một ô trống. Điều này giúp hàm SUMIFS hiểu đúng yêu cầu của bạn và tránh việc trả về kết quả 0 không chính xác.

Ví dụ, nếu bạn muốn tính tổng các giá trị không phải là “Cam”, công thức đúng sẽ là:

=SUMIFS(D2:D7, B2:B7, “<>Cam”)

Trong đó, “<>” là ký hiệu của điều kiện “khác” và cần được bao quanh bởi dấu ngoặc kép để Excel hiểu rằng bạn đang muốn tìm tất cả các giá trị không phải là “Cam”. Nếu không sử dụng dấu ngoặc kép đúng cách, Excel sẽ không hiểu điều kiện và có thể trả về 0 thay vì tính tổng chính xác.

Ví dụ về sử dụng “” để biểu thị ô trống trong hàm Sumifs

Đảm bảo rằng bạn đã kiểm tra và sử dụng đúng cú pháp trong hàm SUMIFS để kết quả trả về là chính xác, giúp bạn làm việc hiệu quả hơn với dữ liệu trong Excel.

5. Lưu ý khi dùng hàm Sumifs nhiều điều kiện trong Excel

Khi sử dụng hàm SUMIFS để tính tổng với nhiều điều kiện trong Excel, bạn cần lưu ý một số điểm quan trọng để đảm bảo hàm hoạt động chính xác:

• Mỗi ô trong sum_range sẽ chỉ được tính vào tổng nếu nó đáp ứng tất cả các điều kiện đã đặt ra. Điều này có nghĩa là các điều kiện được áp dụng theo logic AND (tức là điều kiện 1 VÀ điều kiện 2, v.v…).
• Nếu sum_range chứa các giá trị TRUE/FALSE, Excel sẽ tự động quy đổi: TRUE được coi là 1 và FALSE được coi là 0.
• Các phạm vi criteria_range (phạm vi điều kiện) phải có số hàng và số cột bằng với sum_range. Nếu kích thước các phạm vi không khớp, Excel sẽ báo lỗi #VALUE!. Các phạm vi này không cần nằm liền kề nhau, nhưng kích thước phải đồng nhất.
• Chuỗi văn bản hoặc điều kiện so sánh (> < >= <=) phải được đặt trong dấu ngoặc kép (“”), ví dụ: “Táo”, “>32”, “jap*” (ký tự * là ký tự đại diện).

Chuỗi văn bản sử dụng ký tự đại diện

• Tham chiếu ô trong điều kiện không cần dấu ngoặc kép, mà phải nối bằng dấu &, ví dụ: “<“&A1 (nếu A1 chứa giá trị 50, thì điều kiện sẽ là <50).
• Hàm SUMIFS không thể xử lý mảng trực tiếp, có nghĩa là bạn không thể sử dụng các hàm khác như YEAR, MONTH, LEFT… bên trong criteria_range. Nếu bạn muốn lọc dữ liệu theo năm, tháng hoặc các điều kiện phức tạp khác, hãy sử dụng hàm SUMPRODUCT thay vì SUMIFS.

Lưu ý quan trọng: Nếu công thức SUMIFS không hoạt động như mong muốn, hãy kiểm tra lại kích thước vùng dữ liệu, cách nhập điều kiện và đảm bảo không có lỗi về kiểu dữ liệu.

6. Những cách dùng hàm Sumifs trong Excel tốt nhất

6.1 Sử dụng ký tự đại diện

Trong Excel, khi bạn sử dụng hàm SUMIFS để tính tổng các giá trị dựa trên điều kiện, bạn có thể tận dụng các ký tự đại diện để tạo ra những điều kiện linh hoạt và tìm kiếm dữ liệu dễ dàng hơn. Các ký tự đại diện giúp bạn tìm kiếm các kết quả khớp gần giống với điều kiện, nhưng không yêu cầu khớp chính xác từng ký tự.

Có hai ký tự đại diện phổ biến được sử dụng trong điều kiện (criteria) của hàm SUMIFS:

• Dấu chấm hỏi (?): Dấu chấm hỏi dùng để thay thế một ký tự đơn bất kỳ trong chuỗi. Điều này có nghĩa là nếu bạn áp dụng dấu chấm hỏi trong một điều kiện, Excel sẽ khớp với bất kỳ ký tự nào ở vị trí đó.

Dùng dấu chấm hỏi để thay thế ký tự đơn bất kỳ

Ví dụ: Nếu bạn áp dụng điều kiện là “T?m”, Excel sẽ tìm mọi ô chứa các từ như “Tom”, “Tim”, “Tommy”… vì chỉ có một ký tự đơn ở vị trí thứ 2.

• Dấu sao (*): Dấu sao có thể thay thế một chuỗi ký tự bất kỳ, bao gồm không có ký tự nào. Điều này cực kỳ hữu ích khi bạn muốn tìm kiếm các chuỗi có một phần đầu hoặc cuối cố định mà không quan tâm đến những ký tự còn lại.
  • Điều kiện “N*” sẽ khớp với bất kỳ ô nào có từ bắt đầu bằng “N”, chẳng hạn như “Nokia”, “Nike”, “Nancy”, v.v.
  • Điều kiện “*N” sẽ khớp với bất kỳ ô nào có từ kết thúc bằng “N”, chẳng hạn như “Japan”, “Nixon”, “John”, v.v.
  • Điều kiện “*N*” sẽ khớp với tất cả các ô chứa chữ “N” ở bất kỳ đâu trong chuỗi, ví dụ như “London”, “Nashville”, “Banana”, v.v.

Việc sử dụng các ký tự đại diện giúp bạn mở rộng khả năng tìm kiếm và áp dụng các điều kiện linh hoạt trong các bảng dữ liệu, giúp bạn làm việc hiệu quả hơn trong Excel.

6.2 Phân biệt rõ hàm SUMIF và SUMIFS

Hàm SUMIF và hàm SUMIFS trong Excel đều là các hàm tính tổng có điều kiện, giúp người dùng tính tổng các giá trị trong một phạm vi dữ liệu thỏa mãn một hoặc nhiều điều kiện nhất định. Tuy nhiên, mặc dù cả hai hàm này có cách hiển thị khá giống nhau, nhưng chúng lại có sự khác biệt rõ rệt trong cách thức hoạt động và cách áp dụng.

Công thức của 2 hàm Sumif và Sumifs là khác nhau

Trong hàm SUMIF, sum_range là đối số tùy chọn và nằm ở đối số thứ ba (nếu bạn không cung cấp sum_range, Excel sẽ tính tổng trực tiếp trong range). Đối với hàm SUMIFS, sum_range lại là đối số bắt buộc và nằm ở đối số đầu tiên.

Công thức của 2 hàm Sumif và Sumifs là khác nhau

Điều này dễ gây nhầm lẫn cho người dùng khi sử dụng các hàm này, vì vị trí của các đối số thay đổi, mặc dù cấu trúc và chức năng của chúng đều liên quan đến việc tính tổng có điều kiện.

6.3 Sử dụng phạm vi hàm hợp lý

Việc xác định phạm vi dữ liệu (range) một cách chính xác là rất quan trọng để đảm bảo tính toán được thực hiện đúng đắn và kết quả là chính xác. Một trong những yếu tố cần lưu ý là việc sử dụng phạm vi sum_range và criteria_range sao cho phù hợp.

• sum_range: Đây là phạm vi chứa các giá trị mà bạn muốn tính tổng. Phạm vi này cần được xác định rõ ràng, vì nếu phạm vi này không chính xác, kết quả tính tổng có thể bị sai lệch. Đảm bảo rằng các ô trong sum_range là các giá trị số hoặc có thể tính toán được, vì hàm SUMIF và SUMIFS chỉ tính tổng các giá trị số.
• criteria_range: Đây là phạm vi mà bạn sẽ áp dụng các điều kiện kiểm tra. Để đảm bảo rằng hàm hoạt động chính xác, phạm vi criteria_range phải có số hàng và số cột tương ứng với sum_range. Điều này có nghĩa là nếu sum_range có 10 hàng, thì criteria_range cũng phải có 10 hàng để đảm bảo hàm có thể so sánh đúng các điều kiện và tính toán tổng chính xác.

Sử dụng phạm vi hàm hợp lý

Hàm SUMIFS trong Excel là một công cụ mạnh mẽ giúp bạn tính tổng với nhiều điều kiện một cách chính xác và linh hoạt. Khi sử dụng hàm này, bạn cần đảm bảo rằng các phạm vi dữ liệu có cùng kích thước, nhập đúng điều kiện và hiểu rõ cách hoạt động của từng tham số. Hy vọng bài viết này từ AntBook đã giúp bạn hiểu rõ hơn về cách sử dụng hàm SUMIFS Excel để tối ưu công việc!

Ngoài ra, nếu bạn muốn nâng cao kỹ năng Excel và thành thạo các hàm tính toán từ cơ bản đến nâng cao, cuốn sách Excel từ cơ bản đến nâng cao của AntBook sẽ là trợ thủ đắc lực cho bạn. Với hướng dẫn chi tiết, ví dụ thực tế và mẹo hay, cuốn sách giúp bạn áp dụng Excel hiệu quả trong công việc. Mua ngay để làm chủ Excel và tối ưu hóa quy trình làm việc của bạn!