Hướng dẫn cách sử dụng hàm SUMIF từ cơ bản đến nâng cao

Ở bài trước kienit đã chia sẻ về các kiến thức cơ bản về hàm SUMIF là gì và các ví dụ minh họa, tiếp theo mình sẽ nói về cách sử dụng hàm SUMIF từ cơ bản đến nâng cao qua các ví dụ thực tế. Mời các bạn cùng xem các bài tập hướng dẫn sau đây.

1/ Hàm SUMIF với ký hiệu là dấu lớn, nhỏ và bằng

Nếu điều kiện là các dấu logic thì bạn cần nhớ các trường hợp sau đây.

Điều kiện lớn hơn (>)

Công thức ví dụ: =SUMIF(A2:A5,”>5″,B2:B5)

Mô tả: Tính tổng giá trị B2:B5 nếu trong A2:A5 có các tham số thỏa điều kiện lớn hơn 5.

Điều kiện nhỏ hơn (<)

Công thức ví dụ: =SUMIF(A2:A5,”<5″,B2:B5)

Mô tả: Tính tổng giá trị B2:B5 nếu trong A2:A5 có các tham số thỏa điều kiện nhỏ hơn 5.

Điều kiện bằng nhau (=)

Công thức ví dụ: =SUMIF(A2:A5,”=”&D1,B2:B5) hoặc =SUMIF(A2:A5,D1,B2:B5)

Mô tả: Tính tổng giá trị B2:B5 nếu trong A2:A5 có tham số bằng với giá trị trong ô D1

Điều kiện không bằng (<>)

Công thức ví dụ: =SUMIF(A2:A5,”<>”&D1,B2:B5)

Mô tả: Tính tổng giá trị B2:B5 nếu trong A2:A5 có tham số không bằng với giá trị trong ô D1

Điều kiện lớn hơn hoặc bằng (>=)

Công thức ví dụ: =SUMIF(A2:A5,”>=5″,B2:B5)

Mô tả: Tính tổng giá trị B2:B5 nếu trong A2:A5 có các tham số thỏa điều kiện > hoặc = giá trị là 5

Điều kiện lớn hơn hoặc bằng (<=)

Công thức ví dụ: =SUMIF(A2:A5,”<=5″,B2:B5)

Mô tả: Tính tổng giá trị B2:B5 nếu trong A2:A5 có các tham số thỏa điều kiện < hoặc = giá trị là 5

2/ Cách dùng hàm SUMIF so sánh với điều kiện là chữ

Ngoài các ký tự logic, ô điều kiện hàm SUMIF còn cho chứa text

Điều kiện tính tổng bằng

Bằng toàn phần: =SUMIF(A2:A8, “bananas”, C2:C8)

Mô tả: Tính tổng cột C nếu các giá trị trong cột A giống hoàn toàn với chữ bananas, không thêm bớt một ký tự. Các ô chứa “green bananas”, “bananas green” or “bananas!” sẽ không được tính.

Bằng từng phần: =SUMIF(A2:A8, “*bananas*”, C2:C8)

Mô tả: Ngược lại với ở trên, tính tổng cột C nếu các giá trị trong cột A đứng một mình (giống chữ bananas) hoặc có thêm chữ đứng cạnh. Ví dụ “green bananas”, “bananas green” or “bananas!” sẽ được tính.

Điều kiện tính tổng khác

Khác toàn phần: =SUMIF(A2:A8, “<>bananas”, C2:C8)

Mô tả: Tính tổng cột C nếu các giá trị trong cột A khác hoàn toàn với chữ bananas, không thêm bớt một ký tự. Các ô chứa “green bananas”, “bananas green” or “bananas!” sẽ được tính.

Khác từng phần: =SUMIF(A2:A8, “<>*bananas*”, C2:C8)

Mô tả: Ngược lại với ở trên, tính tổng cột C nếu các giá trị giống bananas đứng một mình hoặc có thêm chữ kế bên thì sẽ không được tính. Bao gồm cả bananas, “green bananas”, “bananas green” or “bananas!” sẽ không được tính.

Lưu ý: Hàm này không phân biệt chữ hoa và chữ thường

3/ So sánh với ô điều kiện được tham chiếu

Nếu như bạn muốn so sánh cột này với một ô bên ngoài thì bạn dùng dấu tham chiếu (&)

Ví dụ 1: Tính tổng khối lượng các sản phẩm, điều kiện là sản phẩm nào có mã JH04 sẽ không tính.

Công thức: =SUMIF(B2:B5,”<>”&E2,C2:C5)

Ví dụ 2: Tính tổng khối lượng các sản phẩm, điều kiện là sản phẩm nào có mã giống JH04 sẽ được tính

Công thức: =SUMIF(B2:B5,E2,C2:C5) hoặc =SUMIF(B2:B5,”=”&E2,C2:C5)

4/ Dùng hàm SUMIF với các ký tự đại diện

Có 2 loại ký tự đại diện khi so sánh với chữ đó là (?,*). Trong đó:

• Dấu hoa thị (*) – đại diện cho nhiều kí tự
• Dấu hỏi (?) – đại diện cho một kí tự duy nhất ở một vị trí cụ thể

Ví dụ 3: Tính tổng khối lượng của các sản phẩm có mã hàng hóa là HH. Trong trường hợp này, mình sẽ dùng dấu (*) chặn 2 đầu điều kiện HH – có nghĩa là tính khối lượng các mã đều có chứ HH trong đó.

=SUMIF(B2:B5,”*HH*”,C2:C5)

Lưu ý:

• Nếu như điều kiện “*HH*” có nghĩa là lấy hết những giá trị có chữ HH
• Còn điều kiện “HH*” có nghĩa là lấy các giá trị bắt đầu bằng chữ HH
• Và cuối cùng là điều kiện “*HH” lấy các giá trị kết thúc bằng chữ HH

Ví dụ 4: Nếu như bạn muốn tính tổng các giá trị với điều kiện nhiều ký tự thì bạn dùng (?). Bài toán tính tổng khối lượng các mã hàng hóa có 4 ký tự.

=SUMIF(B2:B5,”????”,C2:C5)

Ví dụ 5: 2 dấu (?,*) sẽ được coi như một giá trị nếu như trước đó bạn dùng dấu (~).  Trong trường hợp này chỉ tính khối lượng của tên hàng hóa ?

=SUMIF(A2:A6,”~?”,C2:C6)

5/ So sánh với điều kiện ô trống hoặc không trống

Khi gặp điều kiện này, các bạn dùng ký tự logic. Trong đó:

• Dấu (“=” hoặc “”) có nghĩa là bằng với khoảng trống
• Dấu (“<>”) có nghĩa là không bằng với khoảng trống

Ví dụ 6: Tính tổng khối lượng các sản phẩm có tên là khoảng trắng. Cấu trúc hàm SUMIF như sau:

=SUMIF(A2:A7,””,C2:C7) hoặc =SUMIF(A2:A7,”=”,C2:C7)

Ví dụ7: Tính tổng khối lượng các sản phẩm có tên không là khoảng trắng. Như vậy tổng khối lượng đã bỏ qua tên giả sản phẩm là khoảng trắng, tính 5 sản phẩm còn lại.

=SUMIF(A2:A7,”<>”,C2:C7)

6/ Cách sử dụng hàm SUMIF với điều kiện thời gian

Thời gian ở đây có hiệu là ngày tháng năm.

Tiêu chí	Công thức ví dụ	Mô tả
Tính tổng các giá trị ô dựa trên 1 ngày tháng nhất định	=SUMIF(B2:B9,”10/29/2014″,C2:C9)	Tính tổng giá trị trong các ô C2:C9 nếu ngày ở ô tương ứng trong cột B là ngày 29/10/2014
Tính tổng các giá trị ô nếu ngày ở ô tương ứng lớn hơn hoặc bằng với ngày đã cho	=SUMIF(B2:B9,”>=10/29/2014″,C2:C9)	Tính tổng giá trị trong các ô C2:C9 nếu ngày ở ô tương ứng trong cột B là từ ngày 29/10/2014 trở về sau (về hiện tại)
Tính tổng các gí trị ô nếu ngày ở ô tương ứng lớn hơn ngày ở 1 ô khác	=SUMIF(B2:B9,”>”&F1,C2:C9)	Tính tổng giá trị trong các ô C2:C9 nếu ngày ở ô tương ứng trong cột B là sau ngày trong ô F1

Ví dụ 8: Tính tổng khối lượng có ngày nhập là 04/21/21

=SUMIF(D2:D7,”=04/21/21″,C2:C7)

Ví dụ 9: Tính tổng khối lượng có ngày nhập là 04/21/21 nhưng tham chiếu đến một ô khác. Bạn vẫn dùng ký tự (&) để tham chiếu đến giá trị khác.

=SUMIF(D2:D7,”=”&F2,C2:C7)

Lưu ý: Điều kiện ngày tháng và ô dữ liệu ngày tháng phải giống nhau. Nếu như khác vị trí ngày tháng năm với nhau thì xem như bằng 0.

Ví dụ 10: Trong trường hợp điều kiện là 2 ngày và bạn phải tính tổng thì bạn dùng phương pháp sau. Tính tổng khối lượng từ ngày 04/21/2021 đến ngày 04/29/2021.

= SUMIF(D2:D7,”>=4/21/2021″,C2:C7) – SUMIF(D2:D7,”>=4/29/2021″,C2:C7)

Ví dụ 11: Nếu như bạn không muốn tính luôn cả 2 ngày 04/21/2021 và 04/29/2021 thì bạn chỉ cần bỏ dấu “=”

= SUMIF(D2:D7,”>4/21/2021″,C2:C7) – SUMIF(D2:D7,”>4/29/2021″,C2:C7)

Ví dụ 12: Trong trường hợp đặc biệt, nếu như ngày cuối cùng của bạn là ngày có trong bảng dữ liệu, muốn lấy giá trị của ngày này bạn phải bỏ dấu bằng ở ngày cuối cùng và ngược lại nếu không muốn thì thêm dấu bằng

= SUMIF(D2:D7,”>4/21/2021″,C2:C7) – SUMIF(D2:D7,”>4/26/2021″,C2:C7)

Ví dụ 13: Nếu như bạn so sánh với tham số thì chỉ cần thêm dấu (&)

= SUMIF(D2:D7,”>”&F2,C2:C7) – SUMIF(D2:D7,”>”&G2,C2:C7)

Nếu như bạn không làm thường xuyên thì nhìn công thức này ở bên ngoài thì rất khó hiểu.

7/ Tính tổng giá trị nhiều cột

Chắc chắn bạn sẽ phải gặp bài toán một sản phẩm nhưng có nhiều giá trị khác ở nhiều cột. Hàm SUMIF chỉ cho phép chọn một dãy để tính tổng. Với trường hợp này thì bạn làm như sau

Ví dụ 14: Tính tổng 3 tháng tên hàng hóa bắt đầu bằng chứ H. Đầu tiên bạn phải tạo 1 cột tổng của các tháng lại, việc còn lại là bạn cứ áp dụng theo cấu trúc của hàm tính tổng có điều kiện này.

=SUMIF(B2:B7,”H*”,E2:E7)

8/ Cách dùng hàm SUMIF giữa 2 sheet

Hàm excel cơ bản này cho phép bạn tính toán giẵ các sheet với nhau.

Ví dụ 15: Bảng dữ liệu ở sheet 1, mình tạo sheet 2 để tính toán dữ liệu

=SUMIF(Sheet1!B2:B7,”=MJ03″,Sheet1!E2:E7)

Chắc chắn bài viết này đã quá nhiều ví dụ để bạn hiểu về cách sử dụng hàm SUMIF. Việc nắm rõ cấu trúc là chưa đủ bởi vì đi sâu, qua các bài toán bạn sẽ thấy cách sử dụng hàm SUMIF không quá dễ.

Các bài tập hàm SUMIF mà mình đưa ra ở trên có thể bạn sẽ gặp nhiều trong công việc. Bạn cần hiểu và thực hành các ví dụ trên và liên quan để thuần thục. Cảm ơn các bạn đã theo dõi bài viết tại website kienit.