Hàm SUMIF có ý nghĩa quan trọng trong Excel, nó giúp tính tổng dãy số với các điều kiện khác nhau. Từ đó người dùng làm việc hiệu quả và dễ dàng hơn. Nếu chưa có kiến thức về hàm này bạn cũng có thể học và tham khảo bài viết dưới đây. Sau đây ký tự đặc biệt xin tổng hợp những kiến thức cơ bản nhất để các bạn trả lời hàm SUMIF là gì? Cách sử dụng hàm SUMIF như thế nào là đúng cách ?. Mời các bạn cùng Kituchat.com theo dõi nhé!
Nội dung chính:
Hàm SUMIF là gì?
Hàm SUMIF là gì? SUMIF là một hàm để tính tổng các ô đáp ứng một tiêu chí duy nhất. SUMIF có thể được sử dụng để tính tổng các ô dựa trên ngày, số và văn bản phù hợp với tiêu chí cụ thể. SUMIF hỗ trợ các toán tử logic (>, <, <>, =) và ký tự đại diện (*,?) Để khớp một phần.
Đừng bỏ lỡ:
- Dùng hàm IF như nào là chính xác nhất.
- Các ví dụ dễ hiểu về hàm Vlookup trong Excel.
- Sử dụng hàm COUNT như nào ?
Trong Microsoft Excel , SUMIF là một công thức được sử dụng để cộng các giá trị lại với nhau trên một phạm vi ô. Hàm SUM cơ bản thêm các giá trị trong một phạm vi ô, bất kể giá trị là gì. Với SUMIF, người dùng có thể xác định các điều kiện cụ thể phải được đáp ứng cho các giá trị trong một phạm vi ô được đưa vào quy trình bổ sung.
Công thức và cú pháp hàm SUMIF
Hàm SUMIF là gì? Công thức như thế nào? Trong bảng tính Excel bạn nhập:
Hàm =SUMIF(range, criteria,sum_range) vào ô bảng tính cần hiển thị kết quả.
Trong đó:
- Range: Là vùng được chọn có chứa các ô điều kiện.
- Criteria: Là điều kiện để thực hiện hàm này.
- Sum_range: Vùng cần tính tổng.
Lưu ý: Do tính toán trong ô của Excel, nên hàm SUMIF tính tổng này trên các phiên bản Excel 2016, Excel 2013, hay các phiên bản đời trước như Excel 2010, 2007, 2003 đều áp dụng cấu trúc hàm như nhau.
Hướng dẫn cách sử dụng hàm SUMIF trong Excel
Hàm Sumif mang đến sự tiện lợi hơn rất nhiều nếu người dùng muốn tính tổng doanh thu của đơn vị, doanh số của nhân viên hoặc doanh thu trong khoảng thời gian nhất định, tổng lương theo điều kiện nào đó,… Mọi yêu cầu này đều dễ dàng, chính xác khi bạn sử dụng hàm SUMIF.
Sử dụng hàm Sumif lớn hơn, nhỏ hơn hoặc bằng
Để có thể sử dụng hàm Sumif cộng các giá trị lớn hơn, nhỏ hơn hoặc bằng một giá trị nhất định, bạn hãy xem xét các công thức Sumif có thể sử dụng sau đây. Một lưu ý nhỏ, trong công thức Sumif của Excel, toán tử so sánh theo sau là số hoặc văn bản phải luôn được đặt trong dấu ngoặc kép (“”)
| Tiêu chí | Nhà điều hành | Ví dụ về Công thức tính | Miêu tả |
| Tính tổng nếu lớn hơn | > | =SUMIF(A2:A10, “>5”) |
Tính tổng các giá trị trên 5 trong các ô A2: A10.
|
| Tính tổng nếu nhỏ hơn | < | =SUMIF(A2:A10, “<10”, B2:B10) |
Tính tổng các giá trị trong các ô B2: B10 nếu giá trị tương ứng trong cột A nhỏ hơn 10.
|
| Tính tổng nếu bằng | = (có thể bỏ qua) | =SUMIF(A2:A10, “=”&D1) hoặc là =SUMIF(A2:A10,D1) |
Tính tổng các giá trị trong ô A2: A10 bằng giá trị trong ô D1.
|
| Tính tổng nếu không bằng | <> | =SUMIF(A2:A10, “<>”&D1, B2:B10) |
Tính tổng các giá trị trong các ô B2: B10 nếu một ô tương ứng trong cột A không bằng giá trị trong ô D1.
|
| Tính tổng nếu lớn hơn hoặc bằng | > = | =SUMIF(A2:A10, “>=5”) |
Tính tổng các giá trị lớn hơn hoặc bằng 5 trong phạm vi A2: A10.
|
| Tính tổng nếu nhỏ hơn hoặc bằng | <= | =SUMIF(A2:A10, “<=10”, B2:B10) |
Tính tổng các giá trị trong các ô B2: B10 nếu giá trị tương ứng trong cột A nhỏ hơn hoặc bằng 10.
|
Sử dụng hàm Sumif với tiêu chí văn bản
Bên cạnh các con số, hàm Sumif còn mở rộng cho người dùng thêm các giá trị khác tùy thuộc vào việc một ô tương ứng trong một cột khác có chứa văn bản nhất định hay không. Một lưu ý nhỏ, bạn cần có các công thức Sumif khác nhau để đối sánh chính xác với từng phần.
| Tiêu chí | Ví dụ về Công thức | Miêu tả |
| Tính tổng nếu bằng | Kết hợp chuẩn xác: =SUMIF(A2:A8, “bananas”, C2:C8) |
Tính tổng các giá trị trong các ô C2: C8 nếu một ô tương ứng trong cột A chứa chính xác từ “chuối” và không có từ hoặc ký tự nào khác. Các ô chứa “chuối xanh”, “chuối xanh” hoặc “chuối!” không bao gồm.
|
| Trận đấu một phần: =SUMIF(A2:A8, “*bananas*”, C2:C8) |
Tính tổng các giá trị trong các ô C2: C8 nếu một ô tương ứng trong cột A chứa từ “chuối”, một mình hoặc kết hợp với bất kỳ từ nào khác. Các ô chứa “chuối xanh” hoặc “chuối xanh” được tổng hợp.
|
|
| Tính tổng nếu không bằng | Kết hợp chuẩn xác: =SUMIF(A2:A8, ” <>bananas”, C2:C8) |
Tính tổng các giá trị trong các ô C2: C8 nếu một ô tương ứng trong cột A chứa bất kỳ giá trị nào khác ngoài “bananas”. Nếu một ô chứa “bananas” cùng với một số từ hoặc ký tự khác như “chuối vàng” hoặc “chuối vàng”, các ô như vậy sẽ được cộng lại.
|
| Trận đấu một phần: =SUMIF(A2:A8, ” <>*bananas*”, C2:C8) |
Tính tổng các giá trị trong các ô C2: C8 nếu một ô tương ứng trong cột A không chứa từ “bananas”, một mình hoặc kết hợp với bất kỳ từ nào khác. Các ô chứa “yellow bananas” hoặc “bananas yellow” không được tổng hợp
|
Mặc khác, công thức “tổng nếu không bằng” được hàm Sumif thể hiện chính xác đang hoạt động. Cụ thể là số lượng trong các kho của toàn bộ sản phẩm đều được bổ sung, trừ “yellow bananas”:
=SUMIF 9A2 : A7, “<> goldfinger bananas”, C2 : C7)
Tương tự như các hàm khác trong Excel, Sumif không phân biệt chữ hoa hay chữ thường. Điều này có nghĩa là “<> banana”, “<> Bananas” và “BANANAS” sẽ được tạo ra chính xác cùng một kết quả
Sử dụng hàm Sumif cùng toán tử so sánh với tham chiếu ô
Khi bạn có nhu cầu nâng cao, thực hiện những bước xa hơn, phổ biến hơn trong công thức Excel Sumif, bạn có thể thay thế giá trị số hoặc văn bản trong tiêu chí bằng một tham chiếu ô. Cụ thể công thức như sau: =SUMIF (A2 : A8, “<>”&F1, C2 : C8)
Khi sử dụng biểu thức logic với tham chiếu ô, bạn bắt buộc phải sử dụng dấu ngoặc kép (“”) để bắt đầu chuỗi văn bản và dấu và (&) để kết nối và kết thúc chuỗi
Ở trường hợp này, bạn sẽ không thay đổi công thức thành các giá trị tổng có điều kiện dựa vào một tiêu chí khác. Ngược lại, bạn chỉ cần nhập một giá trị mới vào một ô được tham chiếu.
Toán tử so sánh “bằng với” (=) có thể bị bỏ qua trước tham chiếu ô. Do vậy, cả hai công thức dưới đây đều tương đương nhau và đúng
- 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 các ký tự đại diện
Khi bạn tính tổng các ô có điều kiện dựa trên tiêu chí “văn bản” hoặc tính tổng bằng đối sánh từng phần thì bạn phải sử dụng các ký tự đại diện trong công thức của hàm Sumif.
Những ký tự đại diện có sẵn mà bạn có thể sử dụng là:
- Dấu hoa thị (*): đại diện cho bất kỳ số ký tự nào
- Dấu chấm hỏi (?): đại diện cho một ký tự duy nhất ở một vị trí cụ thể
Ví dụ: tính tổng các giá trị dựa trên đối sánh từng phần
Khi bạn muốn tính tổng số tiền liên quan đến tất cả các loại “Banana”. Công thức của hàm Sumif sẽ được thể hiện như sau:
- Tiêu chí bao gồm văn bản kèm theo dấu (*): =SUMIF (A2 : A8, “*bananas*”, C2 : C8)
- Tiêu chí bao gồm một tham chiếu ô kèm theo dấu (*): =SUMIF (A2 : A8, “*”&F1&”*”, C2 : C8). Ở đây, bạn nên lưu ý việc sử dụng dấu và (&) trước và sau tham chiếu ô để nối một chuỗi
Để bạn sử dụng các ký tự đại diện với các ô tham thiếu, bạn nên kết hợp chúng với toán tử nối (&). Nếu bạn chỉ muốn đếm những ô bắt đầu hoặc kết thúc bằng văn bản nhất định, bạn chỉ cần thêm một dấu (*) vào trước hoặc sau văn bản:
- Tính tổng các giá trị trong C2 : C8 nếu một ô tương ứng trong cột A bắt đầu bằng từ “bananas”: =SUMIF (A2 : A8, “bananas*”, C2 : C8)
- Tính tổng các giá trị trong C2 : C8 nếu một ô tương ứng trong cột A kết thúc bằng từ “bananas”: =SUMIF (A2 : A8, “*bananas”, C2 : C8)
Sử dụng hàm Sumif tính tổng các số lớn nhất hoặc nhỏ nhất trong phạm vi
Hàm SUMIF là gì? Khi bạn muốn cộng các số lớn nhất hoặc nhỏ nhất trong phạm vi, bạn hãy sử dụng hàm Sum kết hợp cùng hàm Large hoặc Small
Ví dụ: tính tổng một biến số có giá trị lớn nhất hoặc nhỏ nhất
Bạn không muốn rườm rà khi phải thay đổi công thức mỗi khi bạn muốn tính tổng một số ô khác nhau. Lúc này, bạn có thể đặt số đó vào một số ô thay vì nhập trực tiếp chúng vào công thức. Tiếp đó, bạn hãy sử dụng hàm Row và Indirect, tham chiếu đến ô chứa biến, ô E1 như trường hợp cụ thể dưới đây:
- Tính tổng một số giá trị hàng đầu có thể thay đổi: =SUM (LARGE (B1 : B50, ROW (INDIRECT (“1 : “&E1))))
- Tính tổng một số giá trị dưới cùng có thể thay đổi: =SUM (SMALL (B1 : B50, ROW (INDIRECT (“1: “&E1))))
Sử dụng hàm Sumif tính tổng các ô tương ứng với các ô trống hoặc với các ô không trống
Bạn sử dụng “=” làm tiêu chí. Bởi khi ô trống được hiểu là hoàn toàn không chứa công thức, không có chuỗi độ dài bằng 0 được trả về từ một số hàm Excel. Cụ thể công thức Sumif như sau: =SUMIF (A2 : A10, “=”, C2 : C10)
Nếu “trống” bao gồm các chuỗi có độ dài bằng 0 thì bạn hãy sử dụng dấu “” làm tiêu chí: =SUMIF (A2 : A10, “ “, C2 : C10)
Sử dụng hàm Sumif với ngày tháng
Khi sử dụng hàm Sumif để tính tổng có điều kiện các giá trị dựa trên ngày tháng khá tương đồng với cách sử dụng tiêu chí văn bản và số. Nếu bạn muốn tính tổng những giá trị tương ứng với ngày lớn hơn, nhỏ hơn hoặc bằng này bạn chỉ định, bạn có thể sử dụng các toán tử so sánh để thực hiện. Cụ thể một ví dụ về công thức hàm Sumif trong trường hợp này như sau:
| Tiêu chí | Ví dụ về Công thức | Sự miêu tả |
| Tính tổng các giá trị dựa trên một ngày nhất định. | =SUMIF(B2:B9,”10/29/2014″,C2:C9) |
Tính tổng các giá trị trong ô C2: C9 nếu ngày tương ứng trong cột B là 29-10-2014.
|
| Tính tổng các giá trị nếu một ngày tương ứng lớn hơn hoặc bằng một ngày nhất định. | =SUMIF(B2:B9,”>=10/29/2014″,C2:C9) |
Tính tổng các giá trị trong ô C2: C9 nếu ngày tương ứng trong cột B lớn hơn hoặc bằng 29-10-2014.
|
| Tính tổng các giá trị nếu một ngày tương ứng lớn hơn một ngày trong ô khác. | =SUMIF(B2:B9,”>”&F1,C2:C9) |
Tính tổng các giá trị trong các ô C2: C9 nếu ngày tương ứng trong cột B lớn hơn ngày trong F1.
|
Các ví dụ cách sử dụng hàm SUMIF trong Excel
Ví dụ 1: Hãy sử dụng hàm SUMIF để tính tổng giá tiền của quả các thực phầm không nằm trong nhóm nào trong bảng sau:
Bước 1: Trong bảng tính Excel bạn nhập hàm =SUMIF(B2:B8,””,E2:E8) vào ô tham chiếu muốn hiển thị kết quả.
Giải thích hàm:
- SUMIF: Là lệnh hàm tính tổng có điều kiện.
- B2:B8: Là vùng dữ liệu dữ liệu cho điều kiện bài toán.
- “”: Là điều kiện yêu cầu của bài toán cần tìm.
- E2:E8: Là vùng dữ liệu lấy kết quả.
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Ví dụ 2: Hãy sử dụng hàm SUMIF để tính tổng giá tiền của nhóm trái cây trong bảng sau:
Bước 1: Trong bảng tính Excel bạn nhập hàm =SUMIF(B2:B8,”Trái cây”,E2:E8) vào ô tham chiếu muốn hiển thị kết quả.
Giải thích hàm:
- SUMIF: Là lệnh hàm tính tổng có điều kiện.
- B2:B8: Là vùng dữ liệu dữ liệu cho điều kiện bài toán.
- “Trái cây”: Là điều kiện yêu cầu của bài toán cần tìm.
- E2:E8: Là vùng dữ liệu lấy kết quả.
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Ví dụ 3: Hãy sử dụng hàm SUMIF để tính giá tiền lớn hơn 1 ô đã chọn. Ở đây mình chọn giá trị so sánh là 5.000.000
Bước 1: Trong bảng tính Excel bạn nhập hàm =SUMIF(D2:D8,”>”&F2,E2:E8) vào ô tham chiếu muốn hiển thị kết quả.
Giải thích hàm:
- SUMIF: Là lệnh hàm tính tổng có điều kiện.
- D2:D8: Là vùng dữ liệu dữ liệu cho điều kiện bài toán.
- “>”&F2: Là điều kiện yêu cầu của bài toán cần tìm ở ô tham chiếu F2.
- E2:E8: Là vùng dữ liệu lấy kết quả.
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Ví dụ 4: Hãy sử dụng hàm SUMIF để tính giá trị sản phẩm > 3.000.000 trong bảng sau:
Bước 1: Trong bảng tính Excel bạn nhập hàm =SUMIF(D2:D8,”>3000000″,E2:E8) vào ô tham chiếu muốn hiển thị kết quả.
Giải thích hàm:
- SUMIF: Là lệnh hàm tính tổng có điều kiện.
- D2:D8: Là vùng dữ liệu dữ liệu cho điều kiện bài toán.
- “>3000000”: Là điều kiện yêu cầu của bài toán cần tìm.
- E2:E8: Là vùng dữ liệu lấy kết quả.
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Lưu ý khi sử dụng hàm SUMIF
Hàm SUMIF là gì? Sử dụng hàm SUMIF mang đến nhiều thuận tiện. Tuy nhiên, để đạt được điều đó chúng ta phải hiểu bản chất của hàm SUMIF. Và dưới đây là một số lưu ý quan trọng khi bạn sử dụng loại hàm SUMIF điều kiện này:
- Khi sum_range bị bỏ qua, tổng của hàm SUMIF sẽ được tính theo Range.
- Tham chiếu Criteria có chứa chữ hay chứa ký hiệu toán học đều phải dùng ngoặc kép đánh dấu.
- Trong trường hợp phạm vi xác định dạng số mà dữ liệu cung cấp là số thì không cần dùng đến dấu ngoặc kép.
- Trong tham chiếu Criteria có thể dùng các ký tự như ? và *. Tuy nhiên phải dùng đúng trong từng điều kiện cụ thể.
FAQs về hàm Sumif trong Excel
Những lý do khiến công thức Sumif của bạn không thể hoạt động?
Hàm Sumif trong Excel của bạn không thể hoạt động, nó có thể xuất phát từ một số lý do như sau:
- Những tham số dải ô và sum_range phải là dải ô, không phải là mảng
- Cách tính tổng các giá trị từ những trang tính hoặc sổ làm việc khác, miễn là chúng đang mở
- Bạn cần đảm dải ô và sum_range có kích thước bằng nhau
Có thể kết hợp các hàm Sumif và Vlookup không?
Bạn có thể kết hợp hàm Sumif và Vlookup. Trong trường hợp bảng dữ liệu cần tìm đối tượng, dữ liệu có điều kiện thì hai hàm này khi kết hợp sẽ giúp bạn tìm kiếm dữ liệu nhanh chóng, kết quả chính xác ngay cả khi thay đổi đối tượng. Đặc biệt, bạn không cần phải gõ lại công thức.
Như vậy các bạn đã trả lời được câu hỏi hàm SUMIF là gì? Cũng như cách sử dụng hàm SUMIF trong Excel tính thời gian thực có ví dụ minh họa. Hy vọng qua bài viết ki tu dac biet đã giúp bạn thực hiện được hàm SUMIF trong công việc cũng như học tập và nếu bạn có góp ý hãy để lại bình luận bên dưới và đứng quên chia sẻ nếu thấy hữu ích bạn nhé.