Chia sẻ cách sử dụng hàm sumif trong excel 

Xem nhanh

• Giải thích về hàm sumif trong excel
• cách sử dụng hàm sumif trong excel
• Áp dụng điều kiện hàm SUMIF:
• Hạn chế trong cách sử dụng hàm sumif trong excel
• Ví dụ minh họa cho cách sử dụng hàm sumif trong excel
• Ví dụ trong bảng tính:
• Áp dụng điều kiện không bằng
• Áp dụng điều kiện ô trống
• Áp dụng với điều kiện ngày tháng
• Áp dụng với dữ liệu ký tự đặc biệt
• Thận trọng khi dùng phạm vi tính trung bình trong SUMIF:

Hàm SUMIF trong Excel trả về tổng của các ô đáp ứng một điều kiện duy nhất. Điều kiện có thể được áp dụng cho ngày tháng, số và văn bản. Hàm SUMIF hỗ trợ các toán tử logic (>,<,<>,=) và ký tự đại diện (*,?) để so khớp một phần. Cách sử dụng hàm sumif trong excel là một hàm có sự nâng cao hơn so với những hàm cơ bản khác. Hãy tập sử dụng hàm này thêm nhiều lần để bạn có thể thành thục hơn. 

Giải thích về hàm sumif trong excel

Mục đích: Tính tổng các ô trong một phạm vi đáp ứng điều kiện nhất định.

Giá trị trả về: Tổng của các ô thỏa mãn điều kiện.

Cú pháp: =SUMIF(range, criteria, [sum_range])

Trong đó:

• range: Phạm vi ô cần áp dụng điều kiện.
• criteria: Điều kiện cần thỏa mãn.
• [sum_range]: Phạm vi ô cần tính tổng (tùy chọn). Nếu không nhập, Excel sẽ tính tổng các ô trong phạm vi range.

cách sử dụng hàm sumif trong excel

Hàm SUMIF tính tổng các ô trong một phạm vi đáp ứng một điều kiện duy nhất, được gọi là tiêu chí. Đây là một hàm phổ biến và được sử dụng rộng rãi trong Excel, có thể dùng để tính tổng các ô dựa trên ngày tháng, giá trị văn bản và số. Lưu ý rằng SUMIF chỉ có thể áp dụng một điều kiện. Để tính tổng các ô sử dụng nhiều điều kiện, hãy dùng hàm SUMIFS.

Áp dụng điều kiện hàm SUMIF:

Hàm SUMIF hỗ trợ các toán tử logic (>,<,<>,=) và ký tự đại diện (*,?) để so khớp một phần. Điểm khó trong việc sử dụng hàm SUMIF là cú pháp cần thiết để áp dụng điều kiện. Điều này là do SUMIF nằm trong nhóm tám hàm chia tách tiêu chí logic thành hai phần: phạm vi và tiêu chí. Do thiết kế này, các toán tử cần được đặt trong dấu ngoặc kép ("").

Bảng dưới đây hiển thị các ví dụ về cú pháp cần thiết cho các tiêu chí phổ biến:

Lưu ý hai ví dụ cuối cùng liên quan đến việc kết hợp với ký tự "&". Bất cứ khi nào bạn sử dụng giá trị từ một ô khác hoặc sử dụng kết quả của một công thức trong tiêu chí với một toán tử logic như "<", bạn sẽ cần kết hợp chúng. Điều này là do Excel cần đánh giá các tham chiếu ô và công thức để lấy giá trị trước khi giá trị đó có thể được nối với toán tử.

Hạn chế trong cách sử dụng hàm sumif trong excel

Chỉ hỗ trợ một điều kiện: SUMIF chỉ có thể áp dụng một điều kiện duy nhất. Nếu bạn cần tính tổng các ô sử dụng nhiều điều kiện, hãy sử dụng hàm SUMIFS.

Yêu cầu phạm vi thực: Đối số range trong SUMIF phải là một phạm vi thực tế trên trang tính, không thể thay thế bằng một mảng. Điều này có nghĩa là bạn không thể thực hiện những việc như trích xuất năm từ một phạm vi chứa ngày tháng bên trong hàm SUMIF. Nếu bạn cần thao tác với các giá trị xuất hiện trong đối số trước khi áp dụng điều kiện, hãy sử dụng hàm SUMPRODUCT linh hoạt hơn.

Ví dụ minh họa cho cách sử dụng hàm sumif trong excel

Ví dụ 1: Tính tổng các ô lớn hơn 5 trong phạm vi A1:A10:

Sử dụng công thức: =SUMIF(A1:A10, ">5")

Ví dụ 2: Tính tổng các số trong A1:A10 khi màu tương ứng trong B1:B10 là "red":

Sử dụng công thức: =SUMIF(B1:B10, "red", A1:A10)

Lưu ý: Trong ví dụ thứ hai, A1:A10 được nhập vào đối số sum_range vì nó khác với range (B1:B10 chứa tên màu).

Ví dụ trong bảng tính:

Trong bảng tính được hiển thị, có ba công thức SUMIF:

Công thức G5: Tính tổng Doanh số (Sales) khi Tên (Name) bằng "jim".

Công thức: =SUMIF(B5:B15, "jim", D5:D15)

Công thức G6: Tính tổng Doanh số (Sales) khi Tiểu bang (State) bằng "ca" (California).

Công thức: =SUMIF(C5:C15, "ca", D5:D15)

Công thức G7: Tính tổng Doanh số (Sales) lớn hơn 100.

Công thức: =SUMIF(D5:D15, ">100")

Lưu ý:

• Không cần dấu bằng (=) khi xây dựng tiêu chí "bằng".
• SUMIF không phân biệt chữ hoa chữ thường; bạn có thể dùng "jim" hoặc "Jim".
• Công thức cuối cùng không bao gồm sum_range, nên Excel sẽ tính tổng các ô trong range.

Điều kiện trong một ô khác:

Bạn có thể bao gồm giá trị từ một ô khác trong tiêu chí bằng cách sử dụng phép nối. Trong ví dụ dưới đây, SUMIF sẽ trả về tổng của tất cả các doanh số lớn hơn giá trị trong ô G4. Lưu ý toán tử lớn hơn (>), là văn bản, phải được đặt trong dấu ngoặc kép. Công thức trong G5 là:

=SUMIF(D5:D9, ">"&G4) // tính tổng nếu lớn hơn G4

Áp dụng điều kiện không bằng

Để thể hiện tiêu chí "không bằng", hãy sử dụng toán tử "<>" được đặt trong dấu ngoặc kép (""):

=SUMIF(B5:B9, "<>red", C5:C9) // không bằng "red"

=SUMIF(B5:B9, "<>blue", C5:C9) // không bằng "blue"

=SUMIF(B5:B9, "<>"&E7, C5:C9) // không bằng giá trị trong ô E7

Lưu ý lại rằng SUMIF không phân biệt chữ hoa chữ thường.

Áp dụng điều kiện ô trống

SUMIF có thể tính tổng dựa trên các ô trống hoặc không trống. Trong ví dụ sau, SUMIF được dùng để tính tổng các giá trị trong cột C tùy thuộc vào cột D chứa "x" hay trống:

=SUMIF(D5:D9, "", C5:C9) // tính tổng các ô trống

=SUMIF(D5:D9, "<>", C5:C9) // tính tổng các ô không trống

Áp dụng với điều kiện ngày tháng

Cách tốt nhất để sử dụng SUMIF với ngày tháng là tham chiếu đến một ngày hợp lệ trong một ô khác hoặc sử dụng hàm DATE. Ví dụ dưới đây minh họa cả hai phương pháp:

=SUMIF(B5:B9, "<"&DATE(2019,3,1), C5:C9) // tính tổng các ô trước ngày 1/3/2019

=SUMIF(B5:B9, ">="&DATE(2019,4,1), C5:C9) // tính tổng các ô từ ngày 1/4/2019 trở đi

=SUMIF(B5:B9, ">"&E9, C5:C9) // tính tổng các ô sau ngày trong ô E9

Lưu ý: Phải kết hợp toán tử với ngày trong ô E9.

Để sử dụng các tiêu chí ngày tháng phức tạp hơn (ví dụ: tất cả các ngày trong một tháng nhất định hoặc tất cả các ngày giữa hai ngày), bạn sẽ cần chuyển sang hàm SUMIFS, có thể xử lý nhiều tiêu chí.

Áp dụng với dữ liệu ký tự đặc biệt

Hàm SUMIF hỗ trợ ký tự đại diện, như được thấy trong các ví dụ sau:

=SUMIF(B5:B9, "mi*", C5:C9) // bắt đầu bằng "mi"

=SUMIF(B5:B9, "*ota", C5:C9) // kết thúc bằng "ota"

=SUMIF(B5:B9, "????", C5:C9) // chứa 4 ký tự

Dấu ngã (~) là ký tự thoát cho phép bạn tìm kiếm các ký tự đại diện theo nghĩa đen. Ví dụ, để khớp với dấu chấm hỏi (?), dấu hoa thị () hoặc dấu ngã (~) theo nghĩa đen, hãy thêm dấu ngã phía trước ký tự đại diện (ví dụ: ~?, ~, ~~).

Thận trọng khi dùng phạm vi tính trung bình trong SUMIF:

Hàm SUMIF có một số giả định về kích thước của sum_range (phạm vi tính tổng). Hàm này có thể tự động thay đổi kích thước của sum_range để khớp với range (phạm vi áp dụng điều kiện), sử dụng ô trên cùng bên trái của range như điểm gốc. Trong một số trường hợp, hành vi này có thể tạo ra kết quả trông hợp lý nhưng thực tế lại sai. Bạn có thể tham khảo bài viết này để xem ví dụ minh họa cho vấn đề này.

Để tránh gặp phải vấn đề này, bạn nên đảm bảo sum_range có cùng kích thước với range hoặc sử dụng hàm khác như AVERAGEIFS nếu cần áp dụng nhiều điều kiện.