Hàm SUMIF trong Excel là hàm dùng để tính tổng có điều kiện, với một điều kiện cho trước. Hiểu đơn giản hàm SUMIF chính là sự kết hợp của hàm SUM và hàm IF dùng để tính tổng khi thỏa mãn các điều kiện khác nhau. Hàm SUMIF sẽ tiết kiệm thời gian công sức cho các bạn khá nhiều khi các bạn biết cách vận dụng nó trong công việc. Chẳng hạn như bạn cần tính doanh thu của mặt hàng nào đó, lương của một bộ phận,… khi bạn đã có điều kiện nhất định, thì việc áp dụng SUMIF là một điều nên làm. Bài viết này sẽ giúp các bạn hiểu rõ hơn về hàm SUMIF, biết cách sử dụng trong các điều kiện, và trường hợp khác nhau.
Xem Nhanh Mục Lục
Hàm SUMIF trong Excel
Hàm SUMIF được sử dụng trong các phiên bản của Excel. Trước khi chia sẻ thì mọi người có thể thấy trong Excel có rất nhiều công cụ hỗ trợ việc tính Tổng, chỉ với thao tác gõ =Sum thì sẽ có rất nhiều hàm gợi ý được hiện ra
Nếu các bạn muốn tính toán tổng của phần dữ liệu theo một điều kiện cụ thể thì hàm SUMIF trong excel là một công cụ mà bạn cần. Bài viết này của mình sẽ mô tả cú pháp, hướng dẫn cho các bạn về cách sử dụng và kèm theo đó là ví dụ minh họa vể hàm SUMIF trong excel
Công thức của hàm SUMIF trong Excel là gì
=(range, criteria, [sum_range])
Trong đó:
- Range: Mang tính bắt buộc, là phạm vi ô bạn muốn được đánh giá theo điều kiện được đưa ra. Các ô trong phạm vi phải ở dạng số hoặc mảng, tham chiếu chứa số. Nếu chứa giá trị trống hoặc văn bản bị bỏ qua.
- Criteria: Điều kiện của hàm, Các tiêu chí có thể được cung cấp dưới dạng số, văn bản, ngày, so sánh, ô tham chiếu, hay một hàm Excel khác (với điều kiện khác dạng số cần được đặt trong ngoặc kép “ ”, không phân biệt chữ HOA hay chữ thường)
- Sum_range: Dãy các ô chứa các giá trị cần tính tổng đáp ứng các điều kiện Criteria. Đối số sum_range là đối số có thể có hoặc không trong hàm SUMIF, khi đó Excel sẽ tính tổng các ô tương tự mà theo điều kiện đã đưa ra (hay dễ hiểu hơn sum_range trong trường hợp này excel tự động hiểu là Range).
Khi nào dùng hàm SUMIF trong excel ?
Sau khi có được cú pháp thì liệu có ai thắc mắc về việc lúc nào thì nên sử dụng hàm SUMIF hay không nhỉ? Theo đó, hàm SUMIF trong excel được sử dụng khi muốn tính toán tổng của các dữ liệu đáp ứng Một điều kiện nhất định.
Lưu ý
- Nếu điều kiện ở dạng văn bản hoặc có chứa các dấu so sánh trong toán học như >,<,=,… thì phải được đặt trong dấu nháy kép“”.
- Nếu điều kiện ở dạng số thì dấu nháy kép mang tính chất không bắt buộc.
- Trong trường hợp sum_range bị bỏ qua, khi ấy Excel sẽ mặc định tính tổng của các ô tương tự mà điều kiện đã được chỉ định trong đối số range.
- Hàm SUMIF có hỗ trợ ký tự đại diện, ký tự ?( dùng để khớp với ký tự đơn) hay * ( dùng để khớp với chuỗi ký tự) đều có thể dùng trong Criteria. Trường hợp muốn tìm một dấu ? hay dấu * thực sự, bạn hãy gõ dấu ngã ~ trước ký tự đó.
- Đối số sum_range chỉ cần xác định ô trên cùng bên trái của mảng được tính tổng. Vị trí còn lại thì được xác định bởi kích thước của đối số range. Tức là đối số sum_range không cần phải có cùng kích thước với đối số range.
Ví dụ minh họa:
Cho bảng số liệu, liệt kê các loại trái cây từ các nhà cung cấp khác nhau. Ở cột A, chúng ta có danh sách trái cây, ở cột B có tên nhà cung cấp tương ứng, số lượng từng loại quả ở cột C. Tính tổng Bananas (Chuối) tồn kho là bao nhiêu?
Dựa vào yêu cầu của đề bài đưa ra, chúng ta xác định các đối số
- Range: Là cột sản phẩm A2:A9
- Criteria: Là điều kiện ở ví dụ này chính là “Bananas”, do đã copy tên của điều kiện ra riêng, nên có thể nhập ô tham chiếu F1
- Sum_range: Là cột số lượng
Sau đó chúng ta lắp ghép vào công thức hàm SUMIF =SUM(A2:A9,F1,C2:C9) và nhấn enter có kết quả như bên dưới.
Cách sử dụng của hàm SUMIF trong Excel
Hàm SUMIF với các điều kiện có chứa dấu so sánh trong toán học
Một số điều kiện thường gặp và ví dụ đi kèm, các bạn tham khảo ở bảng phía dưới.
Hàm SUMIF với các điều kiện có chứa văn bản
Trong trường hợp có chứa văn bản thì hàm SUMIF sẽ sử dụng như thế nào? Mỗi yêu cầu các bạn sẽ cần xây dựng một công thức khác nhau đối chiếu chính xác, và các đối số cũng cần xác định rõ ràng. Để giúp các bạn dễ hiểu mình sẽ lấy một ví dụ cụ thể nhé.
Ví dụ 2. Cho số lượng sản phẩm tồn ở các kho như bảng bên dưới.
Trường hợp 1: Tính tổng nếu bằng”=”
– Đối chiếu hoàn toàn : Tính tổng sản phẩm có mã hàng AAA1
Mô tả: Giá trị tính tổng trong các ô E4:E13 nếu ô tương ứng trong cột B chứa chính xác từ “AAA1” và không được phép dư một từ hay kí tự nào. Các ô chứa “AAA5”, “AAB6” hay “AAB2” sẽ không bao gồm trong trường hợp này.
Công thức: = SUMIF( B4:B13,”AAA1″,E4:E13)
– Đối chiếu từng phần: Tính tổng sản phẩm mà mã hàng có chứa ký tự AA
Mô tả: Giá trị tính tổng trong các E4:E13 nếu ô tương ứng trong cột B chứa từ “AA”, đứng độc lập hoặc đi kèm với những từ khác”. Các ô chứa “AAA5”, “AAB6” or “AAB2” sẽ đều được tính tổng. Lúc này chúng ta sử dụng đến ký tự đại diện dấu *.
Công thức: = SUMIF(B4:B13,”AA*”,E4:E13)
Trường hợp 2: Tính tổng nếu không bằng “<>”
– Đối chiếu hoàn toàn: Tính tổng các sản phầm mà mã hàng không có chứa ký tự AAA1
Mô tả: Giá trị tính tổng trong các ô E4:E13 nếu ô tương ứng trong cột B chứa bất kì giá trị nào mà khác “AAA1”. Chẳng hạn như “AAB6”, “AAB2” hay “AAA5” thì chúng vẫn được tính tổng.
Công thức: =SUMIF(B4:B13,”<>AAA1″,E4:E13)
– Đối chiếu từng phần: Tính tổng sản phẩm mà mã hàng không có ký tự AA.
Mô tả: Giá trị tính tổng trong các ô E4:E13 nếu ô tương ứng trong cột A chứa bất kì giá trị nào mà khác “”. Nếu ô chứa “AA” có thêm 1 số ký tự nữa như “AAB6” hay “AAB2” sẽ không thỏa mãn điều kiện này.
Công thức: =SUMIF(B4:B13,”<>AA*”,E4:E13)
Sau khi thực hiện theo các công thức mình đã hướng dẫn ở trên và nhấn enter chúng ta có kết quả như bên dưới. Các bạn có thể kiểm tra lại đáp án bằng cách đối chiếu lại với bảng số liệu đã cho.
Lưu ý: Hàm SUMIF trong Excel không phân biệt chữ hoa với chữ thường, nên bạn nhập a hay A là như nhau.
Hàm SUMIF sử dụng dấu so sánh với ô tham chiếu
Nếu bạn muốn rút ngắn thời gian, hay để công thức của bạn trở lên chuyên nghiệp hơn. Bạn có thể thay thế tiêu chí dưới dạng văn bản hay số bằng một ô tham chiếu.
Như trong ví dụ ở phần đầu, mình đã thay thế “Bananas” bằng ô tham chiếu F1. Vậy khi sử dụng dấu thì thể hiện công thức như thế nào?
Chúng ta sẽ tiếp tục làm với ví dụ 2 ở trên nhé.
Với yêu cầu: Tính tổng các sản phầm mà mã hàng không có chứa ký tự AAA1
Ở trên mình cũng đã hướng dẫn các bạn cách làm rồi. Bây giờ chỉ sửa lại công thức một chút, chỉ cần thay thế ký tự văn bản bằng ô tham chiếu. Chúng ta copy Criteria là AAA1 ra ô G4( ô G4 là ô tham chiếu)
=SUMIF(B4:B13,“<>AAA1”,E4:E13) chuyển thành=SUMIF(B4:B13,“<>”&G4,E4:E13)
Lưu ý:
- Khi kết hợp biểu thức lô gic và ô tham chiếu thì bạn phải sử dụng dấu nháy kép (“”) để bắt đầu chuỗi văn bản, sau đó đến dấu so sánh, kết thúc chuỗi cũng bằng dấu nháy kép, tiếp đến là ký hiệu (&) cuối cùng là ô tham chiếu.
Ví dụ: “<>”&G4
- Nếu biểu thức lô gic có chứa dấu bằng “=” thì có thể bỏ qua trước một ô tham chiếu. Nhưng vẫn đảm bảo tính chính xác cho công thức
Ví dụ: Công thức 1: = SUMIF (B4: B13, “=” & G4, E4: E13)
Công thức 2: = SUMIF (B4: B13, G4, E4: E13)
Hàm SUMIF tính tổng các ô tương ứng với các ô trống và ô không trống
Thế nào là ô trống và ô không trống?
Ở đây trống chính là ô đó không có chứa bất cứ ký tự nào, không có công thức, không có chuỗi chiều dài bằng không, khi đó bạn dùng “=” làm Criteria. Còn nếu trống mà gồm các chuỗi chiều dài bằng không, trường hợp có công thức =””, thì bạn sử dụng “” làm Criteria. Công thức lần lượt như sau:
Công thức 1= SUMIF (B3: B14, “=”, E3:E14)
Công thức 2= SUMIF (A2: A10, “”, C2: C10)
Sử dụng cả hai công thức trên đều tìm thấy nếu có bất kỳ ô trống nào, và các giá trị tương ứng sẽ được tính tổng.
Giả sử với ví dụ 2. Chúng ta bỏ đi mã hàng tại số thứ tự từ thứ 2 đến thứ 7. Và tiến hành tìm tổng sản phẩm có mã hàng đang bị bỏ trống.
Ngược lại với ô trống là các ô không trống, chúng ta sẽ sử dụng “<>” để làm Criteria trong công thức. Giả sử muốn tính tổng các ô có mã hàng, ta có công thức: = SUMIF(B3:B14,”<>”,E4,E13)
Các ví dụ minh họa khác về hàm SUMIF trong Excel
Ví dụ 1:
Sử dụng hàm SUMIF tính tổng phụ cấp cho những người có chức vụ là “Nhân Viên” trong bảng số liệu dưới đây:
Với công thức cho ô D15 là : =SUMIF(C5:C14,”Nhân Viên”,D5:D14)
Ta thu được kết quả như sau: 2500000. Khi chúng ta kiểm tra lại kết quả thì thấy rằng trong VD hàm Sumif này có 5 người có chức vụ là “Nhân viên”, như vậy tổng phụ cấp cho những người đó với kết quả là 2500000 là chính xác.
Ví dụ 2:
Tính tổng số tiền nhân viên Hải đã thu tiền, sử dụng hàm SUMIF trong excel như sau:
Công thức như sau:
=SUMIF(H2:H10,”Hải”,F2:F10), trong đó:
Range: H2:H10 – Vùng chứa tên nhân viên công ty
Criteria: “Hải” (hoặc chọn 1 ô tham chiếu là Hải)– điều kiện để tính tổng
Sum_range: F2:F10 – Vùng chứa số tiền đã thu của tất cả các nhân viên
Ví dụ 3:
Giả sử bạn có danh sách các loại hoa quả như trong cột A và số lượng từng loại hoa quả trong cột C. Bài toán đưa ra đó là tính tổng tồn kho của hàng hóa bananas
Trường hợp này chúng ta sẽ sử dụng hàm SUMIF trong excel như sau:
= SUMIF (A2:A8, “bananas”, C2:C8)
Range: A2:A8
Criteria: “bananas”
sum_range: C2:C8
Hoặc một cách khác, “bananas” có thể được thay thế bằng cách chọn ô tham chiếu là F1, khi đó công thức sẽ là:
= SUMIF (A2:A8, F1, C2:C8)
Lưu ý về hàm SUMIF trong excel:
- sum_range không nhất thiết phải cùng kích thước với Range, nghĩa là chúng có thể khác nhau về số lượng hàng. Tuy nhiên, ô đầu tiên của sum_range phải cùng dòng với ô đầu tiên của Range .
Như trong ví dụ thứ 2, bạn có thể lấy ô C2, hoặc C2: C4, hay thậm chí là C2: C100 làm sum_range, và vẫn cho cùng 1 kết quả. Tuy nhiên, cách làm tốt nhất vẫn là chọn Range và sum_range cùng kích thước.
Các ký tự ? và * là các ký tự có thể được sử dụng trong Criteria.
Dấu chấm hỏi (?) đại diện cho ký tự đơn bất kỳ. Ví dụ trong ví dụ 2 có một loại hàng hóa là Hananas, thì khi bạn gõ công thức = SUMIF (A2:A8, “?ananas”, C2:C8) thì kết quả sẽ là tổng các giá trị của hàng hóa bananas và hananas
Dấu sao (*) đại diện cho chuỗi ký tự bất kỳ.
Trong trường hợp muốn tìm một dấu chấm hỏi (?) hoặc dấu sao (*) thì gõ dấu ngã (~) trước ký tự.
Criteria trong 2 ví dụ trên là các điều kiện đơn giản và dễ dàng cho các bạn hình dung. Mở rộng hơn, hàm SUMIF trong excel thực sự là một công cụ hữu ích khi bạn sử dụng linh hoạt điều kiện Criteria. Điều kiện có thể là số, văn bản, ngày tháng, dạng so sánh (lớn hơn, nhỏ hơn, lớn nhất, nhỏ nhất, bằng, khác), một hàm do đó các bạn có thể linh động để sử dụng với nhiều điều kiện khác nhau.
Tổng kết
Trên đây là những chia sẻ của mình về hàm SUMIF trong excel, hy vọng bài viết của mình có thể giúp các bạn hiểu thêm và sử dụng hàm SUMIF một cách dễ dàng hơn. Cùng theo dõi các bài viết được mình chia sẻ trong phần mục tự học excel thuộc blog hỗ trợ để có thêm nhiều hơn những kiến thức về các hàm và cách sử dụng nhé
