Hàm SUMIF trong Excel Hàm tính tổng có điều kiện

Hàm SUMIF là một hàm Excel giúp bạn tính tổng theo điều kiện cụ thể. Dưới đây là mọi điều bạn cần biết về cách dùng hàm SUMIF trong Microsoft Excel.

Cách dùng hàm SUMIF trong Excel

Hàm SUMIF trong Excel trả về tổng các ô đáp ứng một điều kiện cụ thể. Bạn có thể áp dụng tiêu chí theo ngày, số và text. Hàm Excel này hỗ trợ các phép toán logic (>,<,<>,=) và ký tự đại diện để đối sánh từng phần.

• Mục đích: Tính tổng các số trong phạm vi đáp ứng tiêu chí cần thiết.
• Giá trị trả về: Tổng các giá trị được cung cấp.
• Công thức: =SUMIF (range, criteria, [sum_range]). Trong đó:
  • Range - Phạm vi áp dụng tiêu chí.
  • Criteria - Tiêu chí áp dụng.
  • Sum_range - (Tùy chọn) Phạm vi tính tổng. Nếu bị bỏ qua, các ô trong phạm vi sẽ được cộng lại.
• Phiên bản: Bạn có thể dùng hàm SUMIF trong Excel 2003 trở lên.

Lưu ý khi sử dụng hàm SUMIF trong Microsoft Excel

Như đã nói từ đầu, hàm SUMIF tính tổng các ô trong phạm vi đáp ứng một điều kiện. Đối số đầu tiên là phạm vi áp dụng tiêu chí, thứ hai là tiêu chí, và cuối cùng là phạm vi chứa các giá trị tính tổng. SUMIF hỗ trợ toán tử logic, ký tự đại diện để đối sánh một phần. Tiêu chí có thể dùng một giá trị trong ô khác.

SUMIF nằm trong nhóm 8 hàm trong Excel, chia tiêu chí logic thành 2 phần (phạm vi + tiêu chí). Kết quả, công thức được dùng để xây dựng tiêu chí sẽ khác nhau và SUMIF cần một phạm vi ô cho đối số range, bạn không thể dùng một mảng.

SUMIF chỉ hỗ trợ một điều kiện duy nhất. Nếu cần áp dụng nhiều tiêu chí, hãy dùng hàm SUMIFS.

Ví dụ cách dùng hàm SUMIF trong Excel

Bảng tính

Trong ví dụ worksheet ở bài viết này, có 3 công thức SUMIF. Ở công thức đầu tiên (G5), SUMIF trả về tổng doanh số bán hàng của Jim. Ở cột thứ hai (G6), SUMIF trả về kết quả tổng doanh số bán hàng ở bang CA (California). Ở công thức thứ 3 (G7), SUMIF trả về tổng doanh số bán hàng > 100.

=SUMIF(B5:B15,"jim",D5:D15) // name = "jim"
=SUMIF(C5:C15,"ca",D5:D15) // state = "ca"
=SUMIF(D5:D15,">100") // sales > 100

Lưu ý, bạn không cần dấu bằng khi xây dựng tiêu chí “ngang bằng”. Ngoài ra, lưu ý SUMIF không phân biệt chữ hoa, chữ thường nên bạn có thể dùng jim hoặc Jim. Cuối cùng, nhớ rằng công thức cuối cùng không bao gồm sum_range, so range.

Tiêu chí ở ô khác

Giá trị từ ô khác có thể được bao gồm trong criteria bằng cách dùng phép nối. Ở ví dụ bên dưới, SUMIF tính tổng doanh số bán hàng và cho kết quả ở ô G4. Số lớn hơn (>) là text phải nằm trong dấu trích dẫn. Công thức ở G5 là:

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

Không ngang bằng

Để thể hiện tiêu chí không bằng, dùng toán tử <> trong dấu trích dẫn (“”):

=SUMIF(B5:B9,"<>red",C5:C9) // không bằng với "red"
=SUMIF(B5:B9,"<>blue",C5:C9) // không bằng với "blue"
=SUMIF(B5:B9,"<>"&E7,C5:C9) //  không bằng với E7

Các ô trống

SUMIF có thể tính tổng dựa trên các ô trống hoặc không trống. Ở ví dụ bên dưới, SUMIF được dùng để tính tổng số lượng trong cột C phụ thuộc vào cột D có chứa x hoặc bỏ trống.

=SUMIF(D5:D9,"",C5:C9) // blank
=SUMIF(D5:D9,"<>",C5:C9) // not blank

Ngày

Cách tốt nhất để dùng hàm SUMIF chứa ngày là tham chiếu tới một ngày hợp lệ ở ô khác hoặc dùng hàm DATE. Ví dụ bên dưới hiện cả hai phương pháp:

=SUMIF(B5:B9,"<"&DATE(2019,3,1),C5:C9)
=SUMIF(B5:B9,">="&DATE(2019,4,1),C5:C9)
=SUMIF(B5:B9,">"&E9,C5:C9)

Chú ý, bạn phải nối một toán tử với ngày ở E9. Để dùng nhiều tiêu chí ngày nâng cao hơn, bạn nên dùng hàm SUMIFS.

Ký tự đại diện

Hàm SUMIF trong Excel hỗ trợ các ký tự đại diện như ví dụ ở bên dưới:

=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ự

Tóm lại, những điều bạn cần biết khi dùng hàm SUMIF trong Excel:

• SUMIF chỉ hỗ trợ 1 điều kiện. Dùng hàm SUMIFS cho nhiều tiêu chí.
• Khi sum_range bị bỏ qua, ô trong range sẽ được tính.
• Các chuỗi văn bản theo tiêu chí phải nằm trong dấu (""), ví dụ: "apple", ">32", "ja*"
• Các tham chiếu ô theo tiêu chí không bao gồm trong dấu trích dẫn, ví dụ: "<"&A1
• Các ký tự đại diện ? và * có thể được dùng theo tiêu chí. Dấu hỏi khớp với một ký tự bất kỳ, dấu hoa thị khớp với chuỗi ký tự.
• Để tìm một dấu hỏi hoặc hoa thị, dùng dấu ngã (~) ở phía trước dấu hỏi hoặc hoa thị (ví dụ: ~?, ~*).