Nếu bạn đang phải giải quyết nhiệm vụ tính tổng các dữ liệu thỏa điều kiện trong Excel, thì hàm SUMIFS và SUMIF là những gì mà bạn cần. Hướng dẫn trong bài viết này sẽ giải thích ngắn gọn về cú pháp của hàm và cách sử dụng của chúng...

Với những bạn liên tục phải tính toán các yếu tố công việc bằng excel, việc liên tục bổ sung thêm kiến thức trong việc thực hành với một số ví dụ công thức của hàm SUMIF và SUMIFS trong excel là cực kỳ quan trọng. Nó không những giúp tăng hiệu quả trong quá trình tự học excel cơ bản, mà còn giúp năng suất công việc tăng lên đáng kể. Trong bài viết dưới đây, cùng 123job nghiên cứu về hàm SUMIF, cách sử dụng hàm SUMIFS trong excel và những ví dụ cụ thể về nó từ SUMIF cơ bản tới hàm SUMIFS nâng cao nhé...

I. Phân biệt hàm sumif và sumifs

1. Khái niệm hàm sumif và sumifs

Hàm SUMIF được sử dụng để tính tổng các dữ liệu có điều kiện, dựa trên một điều kiện.

Hàm SUMIFS trong Excel được sử dụng để tìm một tính tổng có giá trị các giá trị dựa trên nhiều điều kiện. So với SUMIF, thì cú pháp của hàm SUMIFS phức tạp hơn một chút.

2. Cú pháp hàm sumif và sumifs

Cấu trúc hàm sumif được thiết lập như sau:

=SUMIF (range, điều kiện, [sum_range])

Cấu trúchàm sumifs trong excel được thiết lập như dưới đây, chú ý rằng các đối số đằng sau có thể được sử dụng để xây dựng hàm sumifs nâng cao, hàm sumifs điều kiện ngày tháng… :

=SUMIFS (sum_range, criteria_range1, criteria1, [criter_range2, criteria2], …)

II. Cách sử dụng hàm sumif và sumifs (thêm ví dụ cụ thể như bài tham khảo)

1. Cách sử dụng hàm sumif

Hãy cùng 123job.vn học excel cơ bản với cách sử dụng hàm SUMIF nhé. 

Hình thức hàm

=SUMIF (range, điều kiện, [sum_range])

  • range – có ý nghĩa là dải của các ô được đánh giá theo điều kiện mà bạn đưa ra, hãy chú ý rằng nó mang tính bắt buộc.
  • criteria – điều kiện cần phải đáp ứng, mang tính bắt buộc
  • sum_range – các ô tính tổng nếu thỏa điều kiện đã được chọn, mang tính tùy chọn.

Giả sử chúng ta có bảng tổng kết các chỉ số về thu nhập và thuế thu nhập cá nhân như sau:

tổng kết các chỉ số về thu nhập và thuế thu nhập cá nhân

Tổng kết các chỉ số về thu nhập và thuế thu nhập cá nhân

Trong Excel bảng được trình bày cụ thể như sau:

Trong Excel bảng được trình bày cụ thể

Trong Excel bảng được trình bày cụ thể

Ví dụ

Sử dụng hàm SUMIF để thực hiện tính tổng thuế thu nhập cá nhân của những người có thu nhập thống kê dưới 50 triệu đồng.

Để giải quyết ví dụ này, về cơ bản ta cần xác định được 3 tham số của hàm SUMIF, cụ thể:

  • Range: Phạm vi cột chứa tổng thu nhập, ở đây là B4:B9
  • Criteria là
  • Sum_range là phạm vi cột chứa thuế thu nhập cá nhân cần tính tổng, ở đây là H4:H9

Khi đó, ta thiết lập công thức: =SUMIF(B4:B9,"

thiết lập công thức

Thiết lập công thức

Nhấn Enter và sẽ nhận được số tiền thuế thu nhập cá nhân cần đóng cho những người có thu nhập dưới 50.000.0000 là 2.229.775.

nhận được số tiền thuế thu nhập cá nhân cần đóng

Nhận được số tiền thuế thu nhập cá nhân cần đóng

Chú ý khi sử dụng hàm SUMIF

  • Khi sum_range bị bỏ qua, tổng của hàm đó sẽ được tính theo Range.
  • Criteria là trường có chứa chữ hay ký hiệu toán học đều phải được đặt cố định trong dấu ngoặc kép "".
  • Phạm vi xác định dạng số của dữ kiện có thể được cung cấp là số sẽ không phải dùng đến những dấu ngoặc.
  • Các ký tự ? và * đều có thể được sử dụng trong Criteria. Một dấu chấm hỏi khớp với bất kỳ ký tự xuất hiện đơn lẻ nào; một dấu sao thì lại phù hợp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.
  • Nếu muốn tính tổng giá trị có nhiều hơn 1 điều kiện, chúng ta cần sử dụng hàm sumifs ví dụ như việc ứng dụng hàm sumifs điều kiện ngày tháng, lãi suất, trạng thái sự vật...

2. Cách sử dụng hàm sumifs tính tổng nhiều điều kiện trong học excel cơ bản

Cấu trúc hàm SUMIFS trong Excel có dạng 

=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…).

Trong đó:

  • Sum_range: là trường của các ô cần tính tổng trong bảng dữ liệu, những giá trị trống và giá trị text sẽ lần lượt được bỏ qua, tham số là dữ kiện bắt buộc phải có.
  • Criteria_range1: phạm vi của dữ kiện cần được kiểm tra bằng điều kiện criteria1, đây hoàn toàn là giá trị bắt buộc.
  • Criteria1: điều kiện cụ thể áp dụng cho criteria_range1, có thể là một số, biểu thức, trực tiếp tham chiếu ô để xác định ô nào trong criteria_range1 sẽ được thực hiện tính tổng, cũng là giá trị mang yếu tố bắt buộc.
  • Criteria_range2,criteria2,…: tùy chọn trực tiếp các phạm vi và điều kiện bổ sung, theo đó tối đa 127 cặp criteria_range, criteria.

Ví dụ về các cách ứng dụng, sử dụng hàm sumifs

Sử dụng, cấu trúc hàm sumifs, hoặc hàm sumifs nâng cao, ta có thể linh hoạt để ứng dụng trong nhiều mục đích ví dụ như hàm sumifs điều kiện ngày tháng, hàm sumifs nâng cao… Trong ví dụ, chúng ta sẽ tính tổng kèm theo một số điều kiện khác nhau với bảng dữ liệu dưới đây.

tính tổng kèm theo một số điều kiện

Tính tổng kèm theo một số điều kiện

Áp dụng cách sử dụng cấu trúc hàm sumifs, tính tổng sản phẩm mà nhân viên Hoài bán ra có đơn giá mặt hàng dưới 400.000đ.

Tại ô nhập kết quả chúng ta sẽ trực tiếp điền công thức 

=SUMIFS(D2:D7,C2:C7,"Hoài",E2:E7,"

Trong đó:

  • D2:D7 là vùng cần thực hiện tính tổng mặt hàng.
  • C2:C7 là vùng chứa các giá trị điều kiện tên nhân viên.
  • “Hoài” là điều kiện cụ thể về tên nhân viên cần tính số mặt hàng chứa trong vùng C2:C7.
  • E2:E7 là điều kiện mặt hàng.
  • “400.000” là điều kiện chứa mặt hàng nằm trọn trong vùng E2:E7.

trực tiếp điền công thức

Trực tiếp điền công thức

Kết quả ra tổng số lượng mặt hàng bằng cách sử dụng hàm sumifs sẽ được trình bày như hình dưới đây.

Kết quả ra tổng số lượng mặt hàng

Kết quả ra tổng số lượng mặt hàng

Ngoài ra, bạn cũng có thể thử ứng dụng về hàm sumifs điều kiện ngày tháng - đây là trường hợp xuất hiện khá nhiều trong tin học văn phòng thường ngày.

Lưu ý về cách sử dụng hàm SUMIFS trong Excel

  • Mỗi ô trong vùng, phạm vi sum_range trong cấu trúc hàm sumifs và hàm sumifs nâng cao sẽ được cộng tổng khi thỏa mãn tất cả các điều kiện tương ứng đã xác định là đúng cho ô đó.
  • Các ô trong sum_range có chứa giá trị TRUE được coi là 1, các ô chứa giá trị FALSE được coi là 0.
  • Mỗi criteria_range phải có cùng kích thước vùng chọn tương ứng giống với sum_range, criteria_range và sum_range phải có cùng thông tin số hàng và cùng số cột.
  • Criteria là trường có thể dùng ký tự dấu hỏi chấm (?) thay cho ký tự đơn, dấu sao (*) thay cho một chuỗi. Nếu điều kiện là dấu hỏi chấm hay dấu sao thì phải nhập thêm dấu ~ ở phía trước, giá trị điều kiện là văn bản để trong dấu “.

Ngoài ứng dụng nêu trên, chúng ta có thể có nhiều ứng dụng hơn cho hàm sumifs ví dụ như hàm sumifs điều kiện ngày tháng, lợi nhuận từng kỳ, trạng thái kinh tế...

III. Cách sửa lỗi #VALUE! lỗi trong hàm SUMIF/SUMIFS

Trong quá trình học excel cơ bản, nhất là tự mày mò về hàm sumif, sumifs, khá nhiều trường hợp thao tác nhầm xảy ra dẫn tới những lỗi sai trong hình thức hiển thị giá trị. Lỗi tường gặp nhất là “#VALUE!”. Dưới đây là 3 cách khắc phục thông thường của lỗi này. 

1. Công thức tham chiếu đến các ô trong sổ làm việc đã đóng

Hàm SUMIF/SUMIFS có thể tham chiếu đến một ô hoặc phạm vi trong một sổ làm việc đóng sẽ dẫn đến lỗi giá trị #VALUE! .

Lưu ý: Đây là một vấn đề đã biết với một vài các hàm Excel khác chẳng hạn như COUNTBLANK COUNTIF, COUNTIFS, để đặt tên cho một vài trường.

Giải pháp: Mở sổ làm việc có thể được biểu thị trong công thức, rồi nhấn vào phím F9 để làm mới công thức.
Bạn cũng có thể tránh vấn đề rắc rối này bằng cách dùng hàm SUM hoặc hàm IF cùng nhau trong một công thức mảng.

2. Chuỗi tiêu chí nhiều hơn 255 ký tự

Hàm SUMIF/SUMIFS trả về kết quả không đúng khi bạn cố gắng so khớp các chuỗi dài hơn 255 ký tự.

Giải pháp: Rút ngắn chuỗi giá trị nếu có thể. Nếu bạn không thể rút ngắn chuỗi, hãy sử dụng hàm CONCATENATE hoặc toán tử Dấu và (&) để ngắt giá trị thành nhiều chuỗi. Ví dụ:

=SUMIF(B2:B12;"chuỗi dài"&"một chuỗi dài khác")

3. Trong hàm SUMIFS, tham số đối trong dải_ô_tiêu_chí không được hiển thị nhất quán với tham đối dải_ô_tổng.

Các tham đối dải ô phải luôn thực sự đồng nhất trong hàm SUMIFS. Điều đó có nghĩa là tất cả những tham đối dải_ô_tiêu_chí và dải_ô_tổng sẽ tham chiếu đến cùng một số hàng và số cột cụ thể.

Trong ví dụ sau đây, công thức của chúng ta được phép để trả về tổng doanh số bán hàng ngày của quả táo trong Bellevue. Đối số sum_range (C2:C10) theo đó không tương ứng với cùng số hàng và cột cụ thể trong các đối số criteria_range (A2:A12 & B2:B12). Bằng cách sử dụng cú pháp = SUMIFS (C2:C10,A2:A12, A14,B2:B12, B14) sẽ dẫn đến kết quả lỗi #VALUE!.

dẫn đến kết quả lỗi #VALUE!

Dẫn đến kết quả lỗi #VALUE!

Giải pháp: Tiếp tục với ví dụ cụ thể này, thực hiện thay đổi dải_ô_tổng thành C2:C12 và thử lại công thức.

IV. Kết luận 

Trên đây là cách học excel cơ bản với hàm SUMIF, thực ra thì nó rất dễ phải không nào? Bạn chỉ cần xác định đúng phạm vi của các cột cần thỏa mãn từng tiêu chí tương ứng, tiêu chí và phạm vi của từng cột cần tính tổng là xong. Nếu cần tính tổng các dữ liệu theo nhiều điều kiện, khi đó thì bạn hãy nhớ sử dụng hàm SUMIFS đã được giới thiệu ở trên nhé, dễ và nhanh hơn nhiều lắm.