Cách sử dụng hàm SUMIFS trong Excel: Tính tổng với nhiều điều kiện

Hàm SUM là một trong những hàm phổ biến nhất trong Excel để tính tổng, tuy nhiên, hàm SUMIFS cung cấp khả năng tính tổng dựa trên nhiều điều kiện khác nhau. Hãy cùng khám phá cách sử dụng hàm SUMIFS trong Excel qua bài viết sau đây!

Hướng dẫn này được thực hiện trên máy tính sử dụng hệ điều hành Windows và phiên bản Excel 2016. Bạn cũng có thể áp dụng trên các phiên bản Excel khác như 2007, 2010, 2013, 2019 và trên MacOS một cách tương tự.

I. Hàm SUMIFS là gì? Ứng dụng của hàm SUMIFS trong Excel

Hàm AVERAGEIFS là công cụ mạnh mẽ để tính trung bình dựa trên nhiều điều kiện khác nhau.

Đây là cú pháp sử dụng hàm AVERAGEIFS: =AVERAGEIFS(phạm_vi, tiêu_chí, [phạm_vi_trung_bình]).

Các thông số cụ thể bao gồm: phạm_vi (bắt buộc), tiêu_chí (bắt buộc), và phạm_vi_trung_bình (tuỳ chọn).

Hàm này kết hợp sức mạnh của AVERAGE và IFS, giúp tính toán một cách linh hoạt và hiệu quả.

Các ghi chú quan trọng khi sử dụng hàm AVERAGEIFS:

• Khi phạm_vi_trung_bình trống hoặc là văn bản, AVERAGEIFS sẽ trả về lỗi #DIV0!

• Nếu một ô trong phạm vi tiêu chí bị bỏ trống, AVERAGEIFS sẽ coi như giá trị 0.

• Các ô chứa TRUE trong phạm vi sẽ được tính là 1; FALSE sẽ tính là 0.

Mua phần mềm Microsoft Office trên Mytour với ưu đãi hấp dẫn!

II. Hướng dẫn sử dụng hàm AVERAGEIFS để tính trung bình theo nhiều điều kiện trong Excel

Ví dụ 1: Tính trung bình với 2 điều kiện là số liệu

Tính điểm trung bình Học kỳ 1 của học sinh có điểm Học kỳ 1 từ 6 đến 9 điểm.

Bước 1: Điền vào ô tính trung bình công thức =AVERAGEIFS(D2:D7;D2:D7;'>6';D2:D7;'<>.

Lưu ý: Hãy nhớ sử dụng dấu , hoặc ; phù hợp với công thức! Để biết cách sử dụng dấu, xem ví dụ ở dòng công thức dưới ô tính toán của bạn nhé!

Giải thích hàm:

• D2:D7: Khu vực cần tính trung bình (Điểm HK1).
• D2:D7;'>6': Điều kiện thứ nhất (Điểm HK1 lớn hơn 6).
• D2:D7;'<>': Điều kiện thứ hai (Điểm HK1 không bằng 9).

Nhập các số đã cho vào công thức để tính trung bình.

Nhấn nút Tính để xem kết quả.

Kết quả tính trung bình là:

7 điểm là kết quả trung bình của các bạn Nguyễn Văn A, Lê Thị B, và Mai Thị H.

Nhấn Enter để xem kết quả tính toán.

Ví dụ 2: Tính trung bình với 2 điều kiện số và chữ.

Bước 1: Nhập công thức =TRUNG_BÌNH_NẾU(E2:E7;C2:C7;'Nam';E2:E7;'>7') vào ô tính trung bình.

Lưu ý: Đừng quên sử dụng dấu , hoặc ; đúng cách cho công thức nhé! Để biết cách sử dụng dấu, hãy xem ví dụ ở dòng công thức dưới ô bạn nhập!

Giải thích hàm:

• E2:E7: Phạm vi cần tính trung bình (Điểm Học kỳ 2).
• C2:C7;'Nam': Là điều kiện thứ nhất (Học sinh nam).
• E2:E7;'>7': Là điều kiện thứ hai (Điểm Học kỳ 2 lớn hơn 7).

Nhập công thức giống như trên ảnh

Bước 2: Nhấn Enter để xem kết quả tính toán.

Giải thích kết quả:

• Ở đây, ví dụ lấy điều kiện là học sinh Nam và điểm Học kỳ 2 > 7 có nghĩa là lấy 8 và 9 điểm.
• Ở Học kỳ 2 có 3 bạn Nam thỏa mãn điều kiện là Lê Văn C, Huỳnh Văn D, Nguyễn Văn M nên sẽ lấy số điểm của 3 bạn để tính trung bình.

Nhấn Enter để xem kết quả tính toán.

Ví dụ 3: Tính trung bình với 3 điều kiện

Đề: Tính điểm trung bình Học kỳ 1 của những học sinh Nữ có điểm Học kỳ 1 > 6, điểm Học kỳ 2 khác nhau.

Bước 1: Nhập công thức =TRUNG_BÌNH_NẾU(D2:D7;C2:C7;'Nữ';D2:D7;'>6';E2:E7;'<>') vào ô tính trung bình.

Lưu ý: Đừng quên sử dụng dấu , hoặc ; đúng cách cho công thức nhé! Để biết cách sử dụng dấu, hãy xem ví dụ ở dòng công thức dưới ô bạn nhập!

Dưới đây là cách giải thích kết quả:

• Ví dụ ở đây chọn điều kiện là học sinh Nữ và điểm HK1 >6 và điểm HK2 <> tức là chọn 7, 8, 9 điểm.
• Ở HK2 có 2 bạn Nữ thỏa mãn điều kiện là Lê Thị B và Mai Thị H nên sẽ lấy số điểm của 2 bạn để tính trung bình.

Nhấn phím Enter để xem kết quả tính toán

Ví dụ 4: Tính trung bình với 3 điều kiện

Tính điểm trung bình HK2 của học sinh Nam với điểm HK1 không giới hạn và điểm HK2 lớn hơn 5.

Nhập công thức =TRUNG_BÌNH_NẾU(E2:E7;C2:C7;'Nam';D2:D7;'<9';e2:e7;'<9')

Lưu ý: Sử dụng dấu , hoặc ; phù hợp với công thức. Xem dòng công thức dưới ô bạn đang nhập để biết cách sử dụng dấu phù hợp.

Giải thích hàm:

E2:E7: Là phạm vi cần tính trung bình (Điểm HK2).

Nhập công thức như trên hình

Bước 2: Nhấn Enter để hiển thị kết quả tính toán.

Giải thích kết quả:

Ở đây ví dụ lấy điều kiện là học sinh Nam, điểm HK1 từ 1 đến 8 và điểm HK2 từ 6 đến 10.

Nhấn Enter để hiển thị kết quả tính toán.

III. Các lỗi thường gặp khi sử dụng hàm AVERAGEIFS

1. Lỗi #DIV/0!

Đây là một lỗi phổ biến khi sử dụng hàm AVERAGEIFS trên một tập tin dữ liệu lớn.

Lỗi #DIV/0!

Nguyên nhân: Không tìm thấy giá trị của điều kiện đã cho trong dữ liệu.

Nguyên nhân gây ra lỗi #DIV/0!

Cách khắc phục: Thay đổi điều kiện.

2. Không chấp nhận ô trống

Nếu bạn áp dụng hàm AVERAGEIFS vào ô trống không có dữ liệu, hàm sẽ coi giá trị trong ô đó là 0 và không tính vào kết quả.