Khám phá sức mạnh hàm SUMIF trong Excel: Từ cơ bản đến nâng cao

SUMIF là một hàm Excel được sử dụng rộng rãi nhờ tính ứng dụng cao. Tuy nhiên, không phải ai cũng biết cách khai thác tối đa tiềm năng của nó. Bài viết này sẽ đi sâu vào hàm SUMIF, giải thích ý nghĩa và hướng dẫn cách sử dụng từ cơ bản đến nâng cao một cách chính xác nhất. Làm chủ hàm SUMIF sẽ giúp bạn đơn giản hóa công việc và giải quyết các bài toán thực tế một cách hiệu quả.

Hàm SUMIF là gì?

Hàm SUMIF lần đầu tiên xuất hiện trong Excel 2007. Sự ra đời của hàm này đã làm dấy lên câu hỏi: Hàm SUMIF khác gì so với hàm SUM, tại sao cần sử dụng SUMIF khi đã có SUM?

SUMIF là hàm dùng để tính tổng các giá trị thỏa mãn một hoặc nhiều điều kiện nhất định. Hàm SUMIF thường được sử dụng để tính tổng các ô dựa trên ngày, số liệu hoặc văn bản, miễn là chúng đáp ứng các điều kiện đã cho. Hàm này cũng hỗ trợ các phép toán logic (>, <, <>, =) và các ký tự đại diện (*, ?) để phù hợp với nhiều tình huống khác nhau.

Cú pháp hàm SUMIF: =SUMIF(range,criteria,sum_range)

  • Range: Vùng chứa điều kiện để xét
  • Criteria: Điều kiện để tính tổng
  • Sum_range: Vùng chứa giá trị cần tính tổng (nếu bỏ qua, Excel sẽ tính tổng các ô trong range)

Nếu bạn cần thao tác với các giá trị trong phạm vi điều kiện (ví dụ: trích xuất năm từ ngày tháng), hãy xem xét sử dụng hàm SUMPRODUCT hoặc FILTER.

Hàm SUMIF là gìHàm SUMIF là gì

Ý nghĩa của hàm SUMIF trong Excel

SUMIF mở rộng khả năng của hàm SUM. Thay vì chỉ tính tổng trong một phạm vi cố định, SUMIF yêu cầu các ô phải đáp ứng các điều kiện được chỉ định trong tham số criteria để được tính tổng.

Hàm SUMIF đặc biệt hữu ích khi bạn muốn tính tổng doanh thu của một chi nhánh, doanh số của một nhóm nhân viên, doanh thu trong một khoảng thời gian cụ thể hoặc tổng lương theo một tiêu chí nào đó.

Cách dùng hàm SUMIF trong Excel (cơ bản đến nâng cao)

Hàm SUMIF với nhiều điều kiện (SUMIFS)

Nếu bạn cần tính tổng các giá trị dựa trên nhiều điều kiện (tương tự như điều kiện OR, tức là chỉ cần một trong các điều kiện được đáp ứng), giải pháp đơn giản nhất là tính tổng các kết quả trả về từ nhiều hàm SUMIF. Excel cung cấp hàm SUMIFS để xử lý trực tiếp trường hợp này.

Tính tổng bằng hàm SUMIF với nhiều điều kiện sử dụng SUMIFS

Ví dụ, tính tổng số sản phẩm do Mike và John cung cấp:

=SUMIFS(D2:D9, C2:C9, “Mike”, C2:C9, “John”)

Hoặc sử dụng công thức sau:

=SUMIF(C2:C9, “Mike”, D2:D9) + SUMIF(C2:C9, “John”, D2:D9)

Hàm SUMIF nhiều điều kiệnHàm SUMIF nhiều điều kiện

Hàm SUMIF đầu tiên sẽ tính tổng số lượng sản phẩm do “Mike” cung cấp, hàm SUMIF thứ hai sẽ tính tổng số lượng sản phẩm do “John” cung cấp, và sau đó hai kết quả này được cộng lại.

Tính tổng bằng hàm SUMIF nhiều điều kiện (nâng cao)

Tính tổng các sản phẩm KTE với điều kiện người cung cấp không phải là “JAMES”.

Thay vì tính tổng bằng hàm SUMIF cho từng người cung cấp (ví dụ: JONE, SCARLET và MICHELS), chúng ta có thể sử dụng công thức sau:

=SUMIFS(C2:C12, A2:A12, “KTE”, B2:B12, “<>James”)

Sử dụng hàm SUMIF nâng caoSử dụng hàm SUMIF nâng cao

  • C2:C12 là phạm vi ô chứa giá trị cần tính tổng.
  • A2:A12 là phạm vi ô chứa tiêu chí 1 (sản phẩm), “KTE” là tiêu chí 1.
  • B2:B12 là phạm vi ô chứa tiêu chí 2 (người cung cấp), “<>James” là tiêu chí 2 (khác “James”).

Nhấn phím Enter để nhận kết quả.

Hàm SUMIF kết hợp VLOOKUP

Ví dụ bài toán doanh thu sử dụng hàm SUMIF và VLOOKUP

Khi bạn có các bảng dữ liệu cần tìm kiếm đối tượng dựa trên điều kiện, việc kết hợp hàm SUMIF và VLOOKUP sẽ giúp tìm kiếm dữ liệu nhanh chóng và chính xác hơn. Xem ví dụ dưới đây để hiểu rõ hơn.

Cho bảng doanh thu gồm ba bảng nhỏ với nội dung khác nhau: Bảng 1 chứa thông tin nhân viên và mã số tương ứng, Bảng 2 chứa mã số nhân viên và doanh số, Bảng 3 là bảng để điền kết quả.

Yêu cầu là điền tên nhân viên và doanh số tương ứng vào Bảng 3. Đồng thời, có thể tra cứu doanh số của các nhân viên khác khi thay đổi tên. Do đó, bạn cần sử dụng cả hai hàm SUMIF và VLOOKUP để tính tổng doanh số của nhân viên dựa trên điều kiện cho trước.

  • Bước 1: Áp dụng công thức vào bảng.

    Công thức: =SUMIF(D:D,VLOOKUP(B12,A3:B7,2,FALSE),E:E)

  • Bước 2: Nhập công thức trên vào ô C12 ở Bảng 3, sau đó điền tên nhân viên muốn tính tổng doanh thu vào ô B12. Ví dụ, tính tổng doanh thu của Phí Thanh Lan.

    Đổi định dạng cột về Number và điều chỉnh để hiển thị dấu phẩy phân cách trong Format Cells. Tùy chỉnh Decimal places theo ý muốn.

  • Bước 3: Bây giờ, bạn có thể thay đổi tên nhân viên mà không cần nhập lại công thức, kết quả vẫn sẽ chính xác.

    Ví dụ, nhập mã số nhân viên MS01 vào ô B12 (tên nhân viên Trần Thu Hà), kết quả doanh thu sẽ được hiển thị chính xác.

Hàm SUMIF kết hợp hàm AND

Giả sử bạn có một bảng liệt kê các loại trái cây từ nhiều nhà cung cấp, với tên quả ở cột A, tên nhà cung cấp ở cột B và số lượng ở cột C.

Bạn muốn tìm tổng số lượng một loại trái cây cụ thể được cung cấp bởi một nhà cung cấp cụ thể (ví dụ: tổng số táo được cung cấp bởi nhà cung cấp Pete). Để bắt đầu, xác định các đối số cho công thức SUMIFS:

  • sum_range – C2:C9
  • criteria_range1 – A2:A9
  • criteria1 – “táo”
  • criteria_range2 – B2:B9
  • criteria2 – “Pete”

Hàm SUMIF và ANDHàm SUMIF và AND

Kết hợp các thông số trên, ta được công thức:

=SUMIFS(C2:C9, A2:A9, “táo”, B2:B9, “Pete”)

Để dễ dàng chỉnh sửa công thức, bạn có thể thay thế các tiêu chí văn bản “táo” và “Pete” bằng các tham chiếu ô. Trong trường hợp này, bạn không cần thay đổi công thức để tính toán số lượng trái cây từ các nhà cung cấp khác nhau:

=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)

Hàm SUMIF và ANDHàm SUMIF và AND

Cách sử dụng Hàm SUMIF với nhiều sheet

Vẫn với bài toán trên, làm thế nào để tìm tổng số lượng táo bán được ở tất cả các tiểu bang trong ba tháng vừa qua?

Như đã biết, kích thước của sum_range được xác định bởi kích thước của tham số range. Do đó, bạn không thể sử dụng công thức sau:

=SUMIF(A2:A9, "táo", C2:E9) vì nó sẽ chỉ cộng các giá trị tương ứng với “táo” trong cột C.

nhiều sheet trong excelnhiều sheet trong excel

Giải pháp đơn giản nhất là tạo một cột phụ để tính tổng số lượng cho mỗi hàng, sau đó tham chiếu cột này trong tiêu chí sum_range.

Đặt công thức SUM đơn giản vào ô F2, sau đó kéo xuống cho toàn bộ cột F: =SUM(C2:E2). Sau đó, bạn có thể viết công thức SUMIF:

=SUMIF(A2:A9, "táo", F2:F9) hoặc =SUMIF(A2:A9, H1, F2:F9)

Trong các công thức trên, sum_range có cùng kích thước với range (1 cột và 8 hàng), do đó kết quả sẽ chính xác. Nếu bạn muốn tính mà không cần cột phụ, có thể viết một công thức SUMIF riêng cho từng cột và sau đó cộng các kết quả lại bằng hàm SUM:

=SUM(SUMIF(A2:A9, I1, C2:C9),SUMIF(A2:A9, I1, D2:D9),SUMIF(A2:A9, I1, E2:E9))

Một cách khác là sử dụng công thức mảng phức tạp hơn (nhớ nhấn Ctrl + Shift + Enter):

{=SUM((C2:C9+D2:D9+E2:E9)*(--(A2:A9=I1)))}

Hàm SUMIF rất hữu ích, phải không? Dạng cơ bản khá dễ sử dụng, nhưng phần nâng cao có thể khó áp dụng hơn. Hãy thực hành từ từ cho đến khi bạn quen hoàn toàn với nó. Chúc bạn thành công với các thủ thuật Excel trên.