cách dùng hàm sumif trong excel tính tổng có điều kiện

Hàm sumif là gì?

Hướng dẫn các công thức tính tổng với hàm sumif trong excel

Hàm sumif là hàm tính tổng có điều kiện trong excel, được sử dụng để tính tổng các ô được lựa chọn thỏa mãn một điều kiện hoặc tiêu chí nhất định nào đó. Các điều kiện, tiêu chí này có thể là ngày, số và text. Ngoài ra, hàm sumif trong excel còn hỗ trợ thực hiện các phép tính logic như =, < >, <, > và các ký tự đại diện như *, ? để đối chiếu, so sánh và trả về kết quả là tổng các giá trị mà mình lựa chọn để tính toán.

Bên cạnh đó, nhiều người vẫn còn đặt nghi vấn về sự khác nhau giữa hàm sumif và hàm sumifs. Thực chất, cả hai hàm đều là hàm tính tổng, tuy nhiên hàm sumif chỉ thực hiện tính toán được trong một điều kiện hoặc tiêu chí nhất định, còn hàm sumifs thì rộng hơn, bao quát hơn và có thể tính tổng các ô theo nhiều tiêu chí khác nhau.

Công thức của hàm sumif

Một hàm sumif sẽ có cấu trúc là: 

= SUMIF(range, criteria, sum_range)

Trong đó: 

• range là phạm vi các ô được chọn để đánh giá theo tiêu chí đặt ra, các ô trong mỗi phạm vi được chọn phải là số, tên, mảng hoặc tham chiếu chứa số. Phạm vi được chọn có thể chứa các ngày ở định dạng chuẩn của excel. Những ô chứa văn bản và ô trống sẽ không được áp dụng trong hàm.
• criteria là điều kiện, tiêu chí xác định các giá trị được tính tổng. Các tiêu chí này có thể là số, ngày, biểu thức, chuỗi văn bản hoặc một hàm excel khác. Lưu ý rằng, tất cả các giá trị thuộc tiêu chí đều phải đặt trong dấu ngoặc kép (“”), ví dụ như “< 5”, “học sinh”,… 
• sum_range là các ô được lựa chọn để tính tổng nếu điều kiện được đáp ứng. Nếu bỏ qua sum_range, những ô trong phạm vi đánh giá sẽ được excel tự động thay thế để tính tổng những ô tương tự theo tiêu chí.

Để hiểu chính xác hơn về các giá trị sử dụng trong hàm và những tiêu chí để thực hiện tính tổng bằng hàm sumif, các bạn theo dõi ví dụ sau đây:

Tính tổng số lượng sản phẩm “banana” trong bảng

Bạn có một bảng danh sách các sản phẩm trong cột A và số lượng sản phẩm tương ứng trong cột C. Hãy sử dụng hàm sumif để tính tổng tất cả lượng hàng còn trong kho của sản phẩm “banana”

Xác định các đối số của hàm sumif đối với các tiêu chí trong đưa ra: 

• range là A2:A8 hoặc bạn có thể viết chữ thường như a2:a8
• criteria là “banana”
• sum_range là C2:C8 hoặc c2:c8

Từ đó, ta bắt đầu áp dụng vào công thức để tính tổng như sau: 

=SUMIF(A2:A8, “banana”, C2:C8)

Nhấn Enter và kết quả của lượng hàng còn tồn kho của “banana” sẽ hiện ra là 1080

Hàm sumif với dấu lớn hơn, nhỏ hơn hoặc bằng 

Từ ví dụ trên, ta có thể đưa ra công thức với tiêu chí khác nhau trong hàm sumif như sau:

Tính tổng nếu lớn hơn (>): Tính tổng các số lớn hơn 5 trong phạm vi A2:A8

• Công thức: =SUMIF(A2:A10, “>5”)

Tính tổng nếu nhỏ hơn (<): Tính tổng các số trong cột B, nếu các ô tương ứng trong phạm vi cột A có giá trị nhỏ hơn 1

• Công thức: =SUMIF(A2:A10, “<10”, B2:B10)

Tính tổng nếu bằng một giá trị nào đó (=): Tính tổng các số trong phạm vi A1:A8 có giá trị bằng D1

• Công thức: =SUMIF(A2:A10, “=”&D1) hoặc =SUMIF(A2:A10,D1)

Tính tổng nếu khác một giá trị nào đó (< >): Tính tổng các ô cột B, nếu các ô tương ứng với cột A có giá trị khác D1

• Công thức: =SUMIF(A2:A10, “<>”&D1, B2:B10)

Tính tổng nếu lớn hơn hoặc bằng (>=): Tính tổng các số lớn hơn hoặc bằng 5 trong phạm vi cột A

• Công thức: =SUMIF(A2:A10, “>=5”)

Tính tổng nếu nhỏ hơn hoặc bằng (<=): Tính tổng các ô cột B, nếu các ô tương ứng với cột A có giá trị nhỏ hơn hoặc bằng 10

• Công thức: =SUMIF(A2:A10, “<=10”, B2:B10)

Ví dụ thực tế: Cũng trong bảng tính trên, bạn muốn tính tổng những lượng hàng còn tồn kho trên 100, khi này: 

• range C2:C8
• criteria là >100
• sum_range trong trường hợp này chính là range nên bạn có thể bỏ qua không cần nhập vào. 

Từ trên, ta có công thức tính tổng những lượng hàng còn tồn kho trên 100 như sau:

=SUMIF(C2:C8, “>100”) sau đó nhấn Enter sẽ ra kết quả chính xác hơn 

Sử dụng hàm sumif để tính tổng theo chuỗi giá trị

Để giúp các bạn hiểu rõ hơn về sử dụng hàm sumif theo chuỗi giá trị, chúng ta sẽ đưa ra ví dụ thực tế bằng bảng thể hiện chi tiết từng loại bananas như sau:  

Tính tổng nếu điều kiện bằng nhau 

Đối chiếu hoàn toàn

• Ta nhập lệnh sumif như sau: =SUMIF(A2:A8, “bananas”, C2:C8)
• Giải thích: Tổng các giá trị trong phạm vi từ ô C2:C8 tương ứng với ô chứa duy nhất từ “bananas” trong cột A, những ô khác mặc dù có thêm “bananas” ở sau nhưng cũng không được tính vào trong trường hợp đối chiếu hoàn toàn này.

Đối chiếu từng phần

• Ta nhập lệnh sumif như sau: =SUMIF(A2:A8, “*bananas*”, C2:C8)
• Giải thích: Tổng các giá trị trong cột C tương ứng với những ô chứa từ “bananas” đứng độc lập hoặc đi kèm với những từ khác trong cột A. Những ô có chứa thêm từ “bananas” đều được tính 

Tính tổng nếu khác điều kiện 

Đối chiếu hoàn toàn

• Ta nhập lệnh sumif như sau: =SUMIF(A2:A8, “<>bananas”, C2:C8)
• Giải thích: Tổng các giá trị trong phạm vi từ ô C2:C8 tương ứng với ô chứa bất kỳ giá trị nào khác “bananas” trong cột A, những ô chứa thêm những ký tự khác như “green banana”, “yellow banana” sẽ được tính trong công thức này.

Đối chiếu từng phần 

• Ta nhập lệnh sumif như sau: =SUMIF(A2:A8, “<>*bananas*”, C2:C8)
• Giải thích: Tổng các giá trị trong phạm vi từ ô C2:C8 tương ứng với ô chứa bất kỳ giá trị nào khác “bananas” trong cột A, những ô chứa thêm những ký tự khác như “green banana”, “yellow banana” sẽ không được tính vào công thức

Sử dụng hàm sumif với dấu so sánh nếu tham chiếu ô

Đối với hàm sumif sử dụng dấu so sánh để tham chiếu ô, thực chất cách này tương tự như ví dụ ở phần công thức tính tổng có điều kiện trên, ta chỉ rút ngắn lại hoặc thay thế giá trị số hoặc văn bản trong tiêu chí bằng một ô tham chiếu như ví dụ sau đây: 

Thay vì nhập lệnh sumif như công thức chung là

 =SUMIF(A2:A8, “<>*apple*”, C2:C8), ta có thể rút ngắn gọn lại như sau: 

= SUMIF (A2: A8, “<>” & F1, C2: C8)

Lưu ý: 

• Khi dùng công thức logic với một tham chiếu ô nào đó, bắt buộc phải sử dụng dấu nháy kép (“”) và dấu & để kết nối/kết thúc một chuỗi
• Đối với công thức có chứa dấu = thì có thể bỏ qua trước một tham chiếu ô trong phần criteria. Ví dụ như hai công thức sau đây đều đúng hoàn toàn: 

Công thức 1: = SUMIF (A2: A8, “=” & F1, C2: C8)

Công thức 2: = SUMIF (A2: A8, F1, C2: C8)

Sử dụng hàm sumif với ký tự đặc biệt 

Nếu bạn muốn sử dụng hàm sumif dựa trên tiêu chí là văn bản và muốn tính tổng các ô từng phần thì các ký tự đặc biệt bắt buộc phải xuất hiện trong công thức.. Các kí tự đó là:

• Dấu * biểu thị cho nhiều ký tự 
• Dấu ? biểu thị cho ký tự duy nhất ở vị trí cụ thể

Ví dụ cụ thể với bảng dưới đây

Ví dụ 1: Tính tổng giá trị dựa trên đối chiếu từng phần

Bạn muốn tính tổng số lượng hàng còn trong kho của tất cả các loại chuối, bạn sử dụng công thức tính tổng từng phần như sau:

=SUMIF(A2:A8, “*bananas*”, C2:C8)

= SUMIF (A2: A8, “*” & F1 & “*”, C2: C8)

Nếu bạn muốn sử dụng hàm sumif để tính tổng những ô bắt đầu hoặc kết thúc bằng một văn bản cụ thể, bạn chỉ cần thêm dấu * ở trước hoặc sau văn bản tương ứng

Ví dụ: 

• Tính tổng những ô bắt đầu bằng bananas: =SUMIF(A2:A8, “bananas*”, C2:C8)
• Tương tự như vậy, với những ô kết thúc bằng bananas:

=SUMIF(A2:A8, “*bananas”, C2:C8)

Ví dụ 2: Tính tổng giá trị dựa trên số ký tự nhất định.

Trong trường hợp này, bạn sử dụng dấu ? ở phần criteria, để tính tổng tất cả các giá trị có chính xác 6 ký tự

=SUMIF(A2:A8, “??????”, C2:C8)

Ví dụ 3: Sử dụng hàm sumif để tính tổng các ô có giá trị tương ứng với giá trị văn bản.

Đối với cách tính tổng trong trường hợp này, hàm sumif sẽ bỏ qua các giá trị không phải là văn bản

Tính tổng các giá trị trong cột C nếu các ô trong cột A chứa duy nhất 1 ký tự 

 =SUMIF(A2:A8, “?*”, C2:C8)

Tính tổng các ô có vẻ như rỗng, chứa các chuỗi có độ dài là 0

 =SUMIF(A2:A8, “*”, C2:C8)

Ngoài ra, nếu bạn muốn sử dụng dâu * và dấu ? như một ký tự bình thường, hãy thêm dấu ~ và trước nó trong phần criteria

 =SUMIF(A2:A8, “~?”, C2:C8)

Sử dụng hàm sumif để tính tổng những ô tương ứng với ô rỗng

Trong bảng trên, ta có thể thấy ở cột A có một vài ô bị trống, không có bất kỳ thông tin nào, do đó, để tính tổng bằng hàm sumif, ta sử dụng một trong hai công thức dưới đây đều đúng:

 =SUMIF(A2:A8, “=”, C2:C8)

hoặc  =SUMIF(A2:A8, “”, C2:C8)

Sử dụng hàm sumif để tính tổng những ô tương ứng với ô không trống 

Để tạo công thức này, hãy sử dụng dấu <> trong phần tiêu chí, tất cả các ô trong phạm vị lựa chọn sẽ được tính tổng, kể cả những ô trống hoặc những chuỗi có độ dài là 0

Ví dụ: Tính tổng doanh số bán hàng cho tất cả các khu vực trong cột B, không tính những ô trống 

Công thức: =SUMIF(B2:B8, “<>”, C2:C8)

Sử dụng hàm sumif với ngày tháng 

Nhìn chung, cách sử dụng hàm sumif để tính tổng có điều kiện dựa trên ngày tháng cũng  tương tự như với các tiêu chí là văn bản và số 

Ví dụ trong bảng trên, cột B thể hiện giá trị ngày tháng, ta sử dụng hàm sumif để tính tổng dựa trên các tiêu chí sau: 

• Tính tổng các giá trị ô dựa trên ngày tháng nhất định: Tính tổng các giá trị trong cột C nếu ngày trong cột B tương ứng là ngày 20/10/2014, ta có công thức như sau: =SUMIF(C2:C9, “29/10/2014”, B2:B9)
• Tính tổng các giá trị ô bắt đầu tính từ một ngày cụ thể trở về sau, ta có công thức sau: =SUMIF(B2:B9,”>=10/29/2014″,C2:C9)
• Tính tổng các giá trị ô nếu ngày ở ô cụ thể lớn hơn ngày của một hôm khác, ta có công thức: =SUMIF(B2:B9, “>”&F1, C2:C9)

Sử dụng hàm sumif để tính tổng trong một vài cột 

Trong bảng trên, bạn không thể nào sử dụng hàm sumif để tính tổng tất cả số táo đã bán trong tổng thời gian qua: =SUMIF(A2:A9, “apples”, C2:C9), bởi vì đích đến của bạn là tính tổng trong cả 3 cột C, D, E chứ không chỉ tính riêng như này.

Do đó, để tính được chuỗi giá trị như này, bạn cần phải sử dụng thêm hàm SUM

Đầu tiên, bạn đặt công thức =SUM(C2:E2) vào cột F

Sau đó, bạn viết công thức hàm sumif đơn giản như sau: 

 = SUMIF (A2: A9, “apples”, F2: F9) 

hoặc = SUMIF (A2: A9, H1, F2: F9)

sẽ đưa ra kết quả vô cùng nhanh chóng và chính xác.

Trên đây là toàn bộ những kiến thức về hàm sumif mà sieusach.ìno đã chia sẻ với bạn. Hy vọng qua bài viết này, bạn có thể hiểu hoàn toàn về công dụng và biết thêm nhiều công thức mới với hàm sumif để giúp cho công việc của bạn trở nên nhẹ nhàng hơn, chinh phục được hoàn toàn excel trong thời gian sớm nhất.

Xem thêm:

• Hàm Left trong Excel là gì? Hướng dẫn sử dụng hàm Left đơn giản
• Hàm Match trong Excel? Cách sử dụng hàm Match trong Excel
• Tìm hiểu chi tiết về cách dùng và cú pháp hàm Average trong Excel
• Hàm Sum trong Excel là gì? Cách sử dụng hàm Sum Excel
• Hướng dẫn cách làm tròn số trong Excel đơn giản dễ hiểu nhất

Nguyễn Tiến Thành

Tôi là Nguyễn Tiến Thành – Tôi đã có nhiều năm kinh nghiệm review đánh giá các loại thiết bị vệ sinh công nghiệp và các mẹo làm sạch. Hy vọng những chia sẻ của tôi sẽ đem lại cho các bạn những thông tin hữu ích hơn.