Hàm SUMIF: Cách sử dụng và ứng dụng hiệu quả trong tính toán

Hàm Sumif trong Excel là một hàm cơ bản và được sử dụng nhiều nhất trong công việc, học tập,…Là một hàm tính tổng một dãy các ô dựa vào một điều kiện cụ thể. Vậy cách dùng hàm Sumif như thế nào? Hãy cùng Tin học Thành Luân tìm hiểu thông qua các ví dụ thực tế trong bài viết này nhé!

Hàm Sumif nếu tách ra sẽ là 2 hàm sum và hàm if nên khi cần tính tổng và có điều kiện thì các bạn sẽ nhớ ngay ra được hàm sumif. Hàm trong Excel khá dễ nhớ, bạn cần tính tổng hay cần làm bất cứ điều gì chỉ cần dịch nó sang tiếng Anh bạn sẽ nhớ ra được hàm cần sử dụng.

Đọc thêm:

• Cách sử dụng hàm sumif trong google sheet đơn giản kèm ví dụ

Giới thiệu về Hàm SUMIF

Định nghĩa hàm SUMIF trong Excel

Hàm sumif là một hàm tính toán trong Excel cơ bản, được sử dụng để tính tổng các giá trị trong một dãy các ô được chọn dựa trên một điều kiện cụ thể. Hàm này có thể được sử dụng để tính toán các giá trị số, ngày tháng hoặc các giá trị văn bản. Với hàm SUMIF, người dùng có thể áp dụng điều kiện tìm kiếm để lọc dữ liệu và tính tổng các giá trị còn lại theo yêu cầu của mình.

Cú pháp và cách viết công thức cho Hàm SUMIF

=SUMIF(range, criteria, [sum_range])

Trong đó:

• Range: là dãy các ô mà bạn muốn áp dụng điều kiện tìm kiếm để tính tổng.
• Criteria: là điều kiện tìm kiếm để xác định các ô cần tính tổng.
• [Sum_range]: là dãy các ô chứa giá trị cần tính tổng. Tham số này là tùy chọn, nếu bạn bỏ qua nó thì hàm SUMIF sẽ tính tổng các ô trong dãy range.

Cách sử dụng Hàm SUMIF và ví dụ thực tế

Hàm SUMIF sử dụng để tính tổng có điều kiện, quan trọng nhất vẫn là phần điều kiện và vùng tính tổng nên khi sử dụng hàm này bạn cần lưu ý cách đặt công thức. Chúng ta sẽ tìm hiểu sâu hơn về cách hoạt động của hàm này cũng như cách viết điều kiện vào hàm SUMIF một cách đúng và chuẩn nhất.

Hàm SUMIF với dấu lớn hơn, nhỏ hơn hoặc bằng

Đây là dạng cơ bản của hàm SUMIF, chúng ta sẽ làm quen với các toán tử như dấu lớn, nhỏ hơn hoặc bằng. Các bạn cùng mình thực hành ví dụ dưới đây nhé.

Đối với dạng bài toán so sánh với dấu nhỏ hơn lớn hơn hoặc bằng các bạn cần phải lưu ý điều kiện so sánh giá trị phải là các giá trị số.

Ví dụ: Giả sử chúng ta có Bảng thanh toán lương tháng 1 năm 2023 như ảnh trên, yêu cầu cần tính tổng lương của những nhân viên có lương lớn hơn 2 triêu đồng.

Nhìn vào bảng này chúng ta có thể nhìn ngay ra được những nhân viên nào có lương trên 2 triệu đồng và bây giờ cần phải lấy ra được những giá trị lương này rồi tính tổng. Ở đây, điều kiện sẽ là “>2000000” do yêu cầu điều kiệ lương lớn hơn 2 triệu, ở những bài toán tương tự bạn hãy dựa vào yêu cầu đề bài để viết điều kiện cho hợp lý.

Chúng ta sẽ có công thức như sau:

Công thức: =SUMIF(G3:G12,”>”&2000000,G3:G12)

Giải thích:

• G3:G12: Vùng cần đặt điều kiện
• “>”&2000000: Điều kiện lớn hơn 2 triệu
• G3:G12: Vùng cần tính tổng

Do điều kiện lương lớn hơn 2 triệu nên khi viết công thức chúng ta sẽ thấy vùng cần đặt điều kiện và vùng cần tính tổng trùng nhau. Còn phần điều kiện các bạn cần lưu ý đưa dấu lớn nhỏ hoặc dấu bằng vào trong dấu ngoặc kép và kết hợp với dấu và như phần giải thích ở phái trên.

Trong trường hợp công thức bị lỗi hãy kiểm tra lại xem các toán tử của bạn đã đầy đủ dấu ngoặc kép hay chưa và có dấu và hay chưa nhé. Và kết quả của công thức trên là 9400000 đồng, bạn có thể kiểm tra xem kết quả có giống nhau hay không nhé.

Hàm SUMIF tính tổng theo điều kiện so sánh với giá trị là chuỗi văn bản cố định

Ở phần trên là điều kiện tính tổng so sánh với số, còn ở đây là điều kiện tính tổng so sánh với giá trị là văn bản, là chữ. Nhưng văn bản sẽ có loại văn bản cố định và văn bản không cố định nghĩa là:

• Điều kiện so sánh là văn bản cố định: chuỗi ký tự sẽ không bị thay đổi và lặp lại trên các dải vùng điều kiện.
• Điều kiện so sánh là văn bản không cố định: chuỗi ký tự sẽ bị thay đổi nhưng vẫn giữ 1 chuỗi ký tự chung giống nhau trong tất cả các dải vùng điều kiện.

Chúng ta sẽ đi vào ví dụ thực tế để xem sự khác nhau giữa 2 điều kiện này nhé:

Giả sử chúng ta cũng có 1 bảng lương tháng 1 năm 2023 như ảnh trên và cần tính tổng lương của các phòng ban gồm phòng kinh doanh, phòng kế toán và phòng kỹ thuật.

Bạn có thể nhìn trực tiếp vào bảng sẽ thấy các chuỗi văn bản phòng ban này lặp lại và không bị khác nhau ví dụ như Kinh doanh hoặc Kế toán không bị thay đổi và chúng cố định đúng không nào? Do vậy đây chính là điều kiện so sánh dạng văn bản cố định. Và chúng ta sẽ có công thức như sau:

Công thức: =SUMIF(E3:E12,”Kinh doanh”,G3:G12)

Giải thích:

• E3:E12: Vùng điều kiện là cột Phòng Ban
• “Kinh doanh”: Điều kiện là Phòng Kinh Doanh
• G3:G12: Vùng cần tính tổng chính là cột lương

Ở đây bạn để ý phần điều kiện là kinh doanh đã đưa vào dấu ngoặc kép không có dấu bằng, đơn giản vì khi bạn so sánh với giá trị là chuỗi ký tự dạng chữ thì bạn chỉ cần đóng ngoặc kép chuỗi ký tự đó là được. Và kết quả sẽ là:

Hàm SUMIF tính tổng theo điều kiện so sánh với giá trị là chuỗi văn bản không cố định

Tiếp theo là phần hàm SUMIF so sánh điều kiện chuỗi văn bản không cố định, cùng mình tham khảo ví dụ dưới đây để hiểu rõ hơn nhé.

Giống như ví dụ trên cũng là tính tổng lương của các phòng ban nhưng lần này bạn có thể thấy các phòng ban đã thêm các mã số đằng sau. Và đây chính là phần điều kiện so sánh văn bản không cố định. Ở đây, khi bỏ các mã số đằng sau phòng ban đi chúng ta sẽ trở về bài toán trên là so sánh điều kiện văn bản cố định.

Chúng ta sẽ có 2 cách để đưa điều kiện trở về dạng so sánh văn bản cố định:

• Dùng hàm xử lý chuỗi để loại bỏ mã số đằng sau phòng ban
• Dùng dấu * làm ký tự đại diện cho mã số đằng sau phòng ban

Cách 1: Dùng hàm xử lý chuỗi

Với cách này, bạn sẽ dùng các hàm xử lý chuỗi như hàm LEFT, hàm RIGHT, hàm MID hoặc hàm LEN. Chúng ta cần kết hợp những hàm này lại mới có thể bỏ ký tự mã số đi được. Đầu tiên, bạn cần biết được tổng số ký tự trong chuỗi phòng ban đó sau đó trừ đi 3 ký tự mã số và dấu cách đằng trước mã số để ra được số ký tự phòng ban ban đầu.

Chúng ta sẽ có công thức như sau:

Ở cách này, chúng ta cần thêm một cột phụ để có thể đưa về phòng ban ban đầu như ảnh trên. Bạn dùng hàm LEFT như sau: =LEFT(E3,LEN(E3)-3) để lấy ký tự phòng ban.

Giải thích công thức: =LEFT(E3,LEN(E3)-3)

• E3: ô phòng ban cần lấy ký tự
• LEN(E3): hàm tính tổng số ký tự trong 1 chuỗi và ở đây là tính tổng chuỗi ký tự cột phòng kinh doanh
• -3: trừ đi 3 ký tự mã số và 1 dấu cách

Và từ đây trở về bài toán hàm SUMIF điều kiện so sánh văn bản cố định rồi, bạn viết công thức như ví dụ trên như sau:

Công thức: =SUMIF(H3:H12,”Kinh doanh”,G3:G12)

Hoặc bạn không dùng cột phụ và muốn tính toán luôn ra kết quả thì bạn sẽ dùng hàm mảng như sau:

Công thức tham khảo: =SUM(IF(LEFT(E3:E12,LEN(E3:E12)-3)=”Kinh doanh”,G3:G12))

Các bạn hãy nhớ khi gõ công thức trên xong ấn tổ hợp phím Ctrl + Shift + Enter để nó tính toán do công thức trên là công thức mảng.

Kết quả của ví dụ này như sau:

Tổng lương phòng Kinh Doanh: 7,350,000 đồng

Tổng lương phòng Kế toán: 3,900,000 đồng

Tổng lương phòng Kỹ thuật: 7,700,000 đồng

Cách 2: Dùng dấu * làm ký tự đại diện

Chắc hẳn một vài bạn đã biết về công dụng của dấu sao trong Excel rồi đặc biệt là công dụng của nó khi đại diện cho 1 chuỗi ký tự đằng trước hoặc đằng sau một chuỗi khác. Ở cách này, khi bạn không muốn tạo bảng phụ hay phải sử dụng tới công thức mảng thì sẽ sử dụng tới dấu * này. Bạn hãy xem công thức dưới đây nhé:

Công thức: =SUMIF(E3:E12,”Kinh doanh*”,G3:G12)

Giải thích:

• E3:E12: Vùng điều kiện ở đây là cột phòng ban
• “Kinh doanh*”: Điều kiện là phòng Kinh doanh và dấu * đại diện cho các ký tự đằng sau nó
• G3:G12: Vùng cần tính tổng

Đến đây chắc hẳn bạn cũng có thể phân biệt được những điều kiện so sánh văn bản như thế nào rồi. Chúng ta sẽ tiếp tục tìm hiểu sâu hơn về hàm SUMIF nhé!

Tính tổng các số lớn nhất hoặc nhỏ nhất

Phần này bạn có thể tham khảo ví dụ sau đây: Giả sử bạn có dãy số và muốn tính tổng 5 số nhỏ nhất trong dãy số đó thì bạn sẽ viết công thức như sau:

Công thức: =SUM(SMALL(B2:B16,{1,2,3,4,5}))

Hàm SMALL sẽ tìm ra 5 số nhỏ nhất và sau đó hàm SUM sẽ làm nhiệm vụ tính tổng còn lại.

Tương tự, thì bạn có thể tính tổng 5 số lớn nhất bằng hàm LARGE như sau: =SUM(LARGE(B2:B16,{1,2,3,4,5}))

Hàm SUMIF là một công cụ mạnh mẽ trong Excel để tổng hợp dữ liệu theo điều kiện. Tuy nhiên, để tránh các lỗi thường gặp, người dùng cần nắm vững cách sử dụng, xác định đúng phạm vi và điều kiện, và kiểm tra kết quả một cách cẩn thận. Các bạn có thể đọc nhiều bài viết về Excel chất lượng khác tại mục Excel cơ bản nhé!