Cách sử dụng hàm SUMIF – từ Cơ bản – Nâng cao (PRO)

Click ngay để download miễn phí

Hàm sumif được dùng để tính tổng có điều kiện trong excel. (Áp dụng: Excel 2003 2007 2010 2013)

Dành cho: Kế toán, Nhân s, Giáo viên, Chủ cửa hàng, doanh nghiệp,…

Lưu ý: Bài viết bao phủ toàn bộ vấn đề liên quan tới hàm sumif nên khá dài. Bạn nên lưu lại bài viết vào trình duyệt chrome của bạn bằng cách nhấn Ctrl + D. Hoặc copy link bài viết và dán vào tường Facebook.

Khi cần dùng thì mở lại để xem nhé, đỡ phải mất công tìm lại.

Các ni dung chính bn s được hc trong bài này gm:

  • Định nghĩa, ví dụ và cú pháp
  • Tại sao phải thêm dấu $ trong công thức đối với vùng điều kiện và vùng tính tổng của hàm sumif
  • Cách tính tổng với điều kiện lớn hơn, nhỏ hơn hoặc bằng một giá trị cho trước
  • Cách sử dụng hàm để tính tổng các giá trị khác với 1 giá trị nào đó.
  • Ứng dụng hàm trong công vic thc tế: Kế toán
  • Ứng dụng hàm trong công vic thc tế: Nhân s
  • Link Download mẫu bài tập hàm đơn giản, điều kiện nhỏ hơn, khác,… của hàm này

 

1. Đnh nghĩa, ví d và cú pháp v hàm sumif

1.1. Đnh nghĩa:

Là hàm excel được sử dụng để tính tng có 01 điu kin trong excel.

Đơn giản như bạn có một bảng kê doanh thu bán hàng của 100 mặt hàng với 500 dòng dữ liệu.

Làm thế nào để trong 2 giây bạn có được doanh số của mặt hàng bất kỳ. Hàm sumif chính là giải pháp cho bạn.

Lưu ý: Cách s dng hàm sumif trong excel 2003 ging excel 2007 2010 2013. Do đó bn ch cn biết cách s dng trong ví d sau là có th làm được trong các phiên bn khác.

Tuy nhiên hàm sumifs (hàm tính tng nhiu điu kin trong excel) thì không th s dng trong excel 2003 được nhé.

 

1.2. Ví d hàm sumif:

bang-luong---ham-sumif---cach-su-dung-ham-sumif

Yêu cu: Tính tổng lương theo từng cấp bậc

Với danh sách 10 người bạn có thể cộng bằng máy tính cho từng cấp bậc. Tuy nhiên, nếu danh sách có +10.000 người thì không thể cộng tay như thế được, bởi:

  • Rất mất thời gian
  • Rủi ro cộng sai là cực lớn

Gii pháp tính tng lương theo cp bc: Sử dụng Hàm sumif.

Trường sẽ hướng dẫn các bạn các dùng hàm tính tổng để xử lý yêu cầu của bài tập trên ở phần 1.3.

1.3. Cú pháp hàm sumif:

= Sumif (Vùng điều kiện, Điều kiện, Vùng tính tổng)

Vùng điu kin: Cột cấp bậc

Điu kin: Cấp bậc (Ví dụ: Nếu tính tổng lương cho Trưởng phòng thì điều kiện là “Trưởng Phòng”)

Vùng tính tng: Cột Tổng thực lĩnh

=> Hàm sumif sẽ được viết lại như sau khi muốn tính tổng lương cho từng cấp bậc.

bang-luong---ham-sumif---cach-su-dung-ham-sumif

bang-luong---ham-sumif---cach-su-dung-ham-sumif-1 hàm sumif

  • Vùng Điu kin: E5:E14 – chính là cột chứa danh sách chức vụ của từng nhân viên
  • Điu kin: “Phó giám đốc” hoặc “Trưởng phòng” hoặc “Nhân viên”
  • Vùng Tính tng: G5:G14 – chính là cột chứa lương của từng nhân viên

Bạn để ý thấy trong công thức hàm sumif ở trong ảnh trên, ad có thêm các dấu $ vào hàm. Vậy ý nghĩa của dấu $ này là gì. Bạn đọc ở phần dưới nhé. Nó rất quan trọng đấy.

Và bạn cũng đừng quên, xuống cuối bài viết để download bài tập mẫu về thực hành. Sau khi đã nghiên cứu kỹ các điểm chính về hàm này được ad nêu sau đây.

1.4. Có 2 cách viết hàm sumif

Trong ví dụ này, ta có hai cách viết công thức hàm sumif để tính tổng lương cho các cấp bậc trong công ty. Hai cách viết này chỉ khác nhau ở ĐIỀU KIỆN tính tổng.

  • Cách 1: Điều kiện tính tổng trong trường hợp này là chuỗi text thể hiện tên của từng loại cấp bậc: Phó giám đốc, Trưởng phòng hoặc nhân viên. Lưu ý khi điền một chuỗi text bất kỳ trong một công thức Excel thì luôn phải để trong dấu ” “.
  • Cách 2: Trong bảng tổng hợp tiền lương theo cấp bậc ta đã có cột cấp bậc là K, ví dụ Trưởng phòng có địa chỉ ô là K11, khi đó thay vì phải viết một chuỗi text dài loằng ngoằng ta chỉ cần viết địa chỉ chứa giá trị của chuỗi text đó.

Mu cht: Hiểu một cách đơn giản: K11 = “Trưởng Phòng”

Ad khuyên các bạn nên làm quen và thật thành thạo cách số 2 bởi bạn sẽ tiết kiệm được hàng tá thời gian. Bằng cách tư duy hệ thống, khoa học sẽ giúp bạn giải quyết được nhiều vấn đề khác trong công việc và cuộc sống.

2. Mt s lưu ý quan trng khi s dng hàm sumif trong excel

2.1. Ti sao phi thêm du $ trong công thc đi vi vùng điu kin và vùng tính tng.

Dấu $ được thêm vào tên dòng để cố định dòng, thêm vào tên cột sẽ cố định cột. Và trong excel ta dùng dấu $ để cố định dòng cột của công thức.

Như vậy ta chỉ cần viết công thức cho ô đầu tiên rồi copy cho tất cả các ô còn lại.

Khi đó bạn sẽ viết được công thức cho hàng triệu dòng chỉ trong nháy mắt.

Đặc biệt khi sử dụng cách 2 để tính tổng tiền lương theo từng cấp bậc. Ta chỉ cần nhập công thức cho dòng đầu tiên (tức là L10 trong ví dụ trên).

Sau đó chỉ cần copy công thức xuống cho các dòng còn lại khi đó điều kiện tính tổng của hàm sumif sẽ thay đổi theo thành L11 hoặc L12.

Khi copy công thức, việc không cố định vùng tính tổng và vùng điều kiện thì sao?

Khi đó địa chỉ vùng sẽ đổi mỗi khi copy sang dòng hoặc cột khác. Điều này dẫn đến kết quả không chính xác.

Xem bài viết: Ý nghĩa ca du $ – c đnh vùng d liu trong excel

2.2. Cách tính tng vi điu kin ln hơn, nh hơn hoc bng mt giá tr cho trước

Trong ví dụ trên, nếu bạn muốn tính lương của tất cả những ai có lương nhỏ hơn 5.000.000 đ ta sẽ làm như thế nào?.

Đa phần người mới sử dụng excel không biết làm thế nào để excel hiểu được ý của mình trong trường hợp này. Ad sẽ giúp bạn giải quyết khó khăn.

bang-luong---ham-sumif---cach-su-dung-ham-sumif

Mấu chốt của hàm sumif trong trường hợp này là điều kiện “nhỏ hơn 5 triệu đồng” viết như thế nào để excel hiểu được. Trong excel thì toán tử >, <, =,… được sử dụng rất nhiều để so sánh 2 hay nhiều giá trị với nhau.

Trong trường hợp này thì toán tử < sẽ giúp các bạn giải quyết được khúc mắc.

Cách sử dụng hàm sumif để tính tổng giá trị với điều kiện lớn hơn, nhỏ hơn như sau:

= Sumif (G5:G14, “<“&5000000,”G5:G14) = 10.800.000 đ.

Lưu ý: Nếu bạn chỉ viết <5000000 trong phần điều kiện của hàm sumif thì excel sẽ không hiểu được bạn muốn gì đâu bạn nhé.

2.3. Cách s dng hàm sumif đ tính tng các giá tr khác vi 1 giá tr nào đó.

bang-luong---ham-sumif---cach-su-dung-ham-sumif

Yêu cầu: Tính tổng lương của cả công ty trừ cấp Trưởng phòng. Ban căng đây, khó phết đấy. Nhưng bạn đừng lo, thủ thuật dùng hàm excel sau đây bạn sẽ giải quyết được khúc xương này.

Khó đy là: Làm thế nào để excel hiểu ta muốn nó tính tổng với điều kiện “KHÁC”

Khác trong excel được viết như sau: <>

Như vậy hàm sumif được viết như thế nào?

= SUMIF (E5:E14, “<>”&”TP”,G5:G14) = 48.200.000 đ

2.4. Tính tổng nâng cao với dấu (*)

Trong ví dụ trên, Trường muốn tính tổng lương thực lĩnh của những người mang họ Phạm chẳng hạn. Thì chúng ta phải viết công thức như thế nào.

Các bạn nhìn hình ở dưới sẽ thấy, nếu chỉ dùng hàm sumif thông thường sẽ rất khó để tính tổng với điều kiện trên. Bởi có nhiều người có họ Phạm như: Phạm Đức Vũ, Phạm Huỳnh Thảo,…

Giải pháp:

Điều kiện tìm kiếm ta sẽ kết hợp với dấu * ngay sau chữ Phạm là được.

Công thức như sau:

= Sumif (D5:D14, “Phạm*”, G5:G14) => Kết quả trả về: 20.500.000 đ

Tương tự bạn có thể dùng công thức trên để tính tổng lương thực lĩnh của những ai là Nhân viên khi vùng điều kiện là cột Mã nhân viên. Tức là ta không cần dùng tới cột Cấp bậc vẫn thống kê được bình thường.

Công thức như sau:

Tổng lương nhân viên = Sumif (C5:C14, “NV*”, G5:G14)

bang-luong---ham-sumif---cach-su-dung-ham-sumif

3. Vn dng hàm vào công vic thc tế

Ad mong muốn chia sẻ với các bạn không chỉ kiến thức cơ bản về hàm excel. Bạn còn phải biết làm sao để vận dụng nó vào công việc giúp cải thiện hiệu suất làm việc.

Và với hàm sumif cũng không phải ngoại lệ. Ad sẽ bổ sung dần dần nội dụng ứng dụng thực tế của hàm này mỗi khi ad có thêm kiến thức mới. Dưới đây là các nội dung cập nhật nhất.

3.1. ng dng hàm trong công vic ca người làm Kế toán.

Nếu là người học và làm kế toán thì phải biết sử dụng excel để phục vụ công việc của mình. Trong các file kế toán excel, có một phần rất quan trọng mà hàm sumif chính là linh hồn của phần đó.

Đó chính là bảng cân đối tài khoản hay còn gọi là bảng cân đối số phát sinh. Đây là báo cáo giúp tính tổng giá trị phát sinh trong kỳ của một tài khoản.

Tổng số phát sinh nợ tài khoản 1111 = Sumif (Cột số tài khoản hạch toán, 1111, Cột số tiền)

Như trong hình dưới đây, Ad đang chọn ô G11 – công thức hiện trên thanh công thức (formula) như sau:

= Sumif (Phatsinh!$F$10:$F33, C11, Phatsinh!$J$10:$J$33)

ham sumif trong ke toan excel 1, Hàm sumif trong kế toán excel

Đây là bảng cân đối số phát sinh của Webkynang.vn trong kỳ kế toán 01/2008. Nhiệm vụ của chúng ta là phải tổng hợp được tổng số tiền phát sinh của từng tài khoản trong kỳ kế toán này. Và đây là ứng dụng tiêu biểu của hàm sumif trong kế toán excel

Phân tích hàm sumif trong ví dụ này như sau:

Vùng điều kiện: Phatsinh!$F$10:$F33

Cột F bên sheet Phatsinh là cột cập nhật số tài khoản phát sinh bên nợ của nghiệp vụ kinh tế phát sinh

F10 -> F33: toàn bộ nghiệp vụ phát sinh trong tháng 01/2008.

Điều kiện: C11

Điều kiện tìm kiếm ở đây là số tài khoản muốn tính tổng giá trị tiền phát sinh trong kỳ kế toán

Vùng tính tổng: Phatsinh!$J$10:$J$33

Cột J là cột số tiền bên sheet Phatsinh.

Như vậy, với việc dùng hàm sumif trong bảng cân đối số phát sinh ta đã có được số liệu phát sinh của từng tài khoản trong kỳ kế toán 01/2008.

Đây là ứng dụng thiết thực nhất của hàm sumif với người làm kế toán. Ngoài ra, khi bạn đã hiểu bản chất và biết cách vận dụng của các trường hợp tiêu biểu, bạn sẽ còn biết cách vận dụng vào nhiều trường hợp khác nữa. Đừng tự giới hạn bản thân nhé bạn của tôi.

3.2. ng dng vào công vic ca người làm Nhân s

Đối với người làm nhân sự, bạn thường xuyên nhận được yêu cầu của cấp trên ví dụ như:

  • Báo cáo cho anh tổng số nhân sự hiện nay là bao nhiêu? Số lượng nhân sự của bộ phận kinh doanh tháng này so với tháng trước tăng như thế nào
  • Cho anh biết chi phí lương của Phân xưởng A, Phân xưởng B tháng này là bao nhiêu.
  • và rất nhiều yêu cầu khác.

Rõ ràng bạn phải có một danh sách bảng lương chi tiết của toàn công ty, sau đó dựa vào từng điều kiện (phòng, phân xưởng,…) để bạn tính số lượng nhân sự, chi phí lương phải không nào.

Khi đó bạn dùng hàm sumif để tính tổng theo điều kiện được yêu cầu.

Cú pháp chung như sau:

  • Tổng lương phòng A = Sumif (Cột phòng ban, “Phòng A”, Cột lương thực lĩnh)
  • Tổng lương Phân xưởng B = Sumif (Cột phân xưởng, “Phân xưởng B”, Cột lương thực lĩnh)
  • Số lượng nhân sự Công đoạn Dệt = Sumif (Cột công đoạn, “Dệt”, Cột số lượng nhân sự)

3.3. Ứng dụng cho việc tổng hợp báo cáo nhập xuất tồn hàng hóa

Trong kỳ ta nhập xuất rất nhiều lần và cũng rất nhiều loại hàng hóa.

Và ta sẽ dùng hàm tính tổng có điều kiện để thống kê số lượng và doanh thu của từng mặt hàng.

Cú pháp hàm như sau:

  • Tổng số lượng mặt hàng A = Sumif (Cột mã hàng, mã hàng A, Cột số lượng)
  • Tổng Doanh thu ngày 15 = Sumif (Cột ngày tháng, Ngày 15, Cột Doanh thu)

Với những ai đang kinh doanh nhỏ muốn theo dõi bán hàng hoặc tồn kho thì phải biết hàm này. Bên cạnh đó là biết cách dùng hàm Sumifs nữa bạn nhé.

Bởi lẽ hàm sumifs cho phép tính tổng nhiều điều kiện, ví dụ như tổng doanh thu theo:

  • Mặt hàng A
  • Từ ngày … tới ngày…
  • Của cửa hàng B

 

4. Download mu bài tp hàm sumif đơn gin, điu kin nh hơn, khác,…

Học mà không hành bạn sẽ không đạt được nhiều thành công. Tương tự trong excel, dù ad có chia sẻ bao nhiêu bí kíp hay đi chăng nữa mà bạn không thực hành và vận dụng vào công việc hàng ngày của bạn thì chỉ sau một hai ngày bạn sẽ quên ngay và luôn thôi.

Vậy thì còn chờ gì nữa, hãy download bài tập thực hành ở link dưới và đừng quên nhấn nút like ở bên trái bài viết để chia sẻ các bí kíp hay này cho bạn bè nhé. Ad xin cảm ơn bạn đã dành thời gian đọc và theo dõi những chia sẻ của ad. Nếu có điểm nào chưa rõ hoặc chưa đúng bạn hãy gửi góp ý về hòm mail: webkynang.vn@gmail.com

Link download bài tp thc hành. 

Pass giải nén: webkynang.vn

Ad sẽ thường xuyên update các bài viết trên website để đem tới nhiều thủ thuật hay tới các bạn. Nên hãy theo dõi thường xuyên nhé.

Hi vọng bài viết chia sẻ vừa rồi sẽ giúp các bạn cải thiện kỹ năng excel của mình. Hãy luôn ủng hộ chuyên trang về excel của webkynang.vn nhé các bạn.

 

Để luôn cập nhật kiến thức về Excel – Bạn nên đăng ký nhận bài viết mới qua email nhé:

Click đ đăng ký

Lưu ý: Sau khi đăng ký, bn hãy vào email va đăng ký đ xác nhn đăng ký (Có th thư kích hot b rơi vào hòm thư spam – bn hãy vào hòm thư spam đ kim tra nhé)


 

Click ngay

7 Comments

  1. TRAM
    • xim
    • Đặng quang tiến
    • Châu Việt Thanh
      • Admin
  2. Trần văn Hưng

Leave a Reply

Time limit is exhausted. Please reload CAPTCHA.