By 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.
What is your reaction?
