SUMIF là hàm phổ biến được nhiều người sử dụng bởi tính hữu dụng cao. Tuy nhiên không phải ai cũng biết khai thác hết tính ứng dụng của nó. Hàm SUMIF là gì, ý nghĩa hàm SUMIF trong Excel và cách dùng hàm SUMIF cơ bản đến nâng cao chuẩn xác nhất là sao. Ở bài viết này chúng ta cùng đi tìm hiểu sâu hơn về hàm SUMIF và cách sử dụng hàm này nhé, sử dụng thành thạo hàm SUMIF sẽ là một thủ thuật Excel khiến công việc và các bài toán trong thực tế của bạn trở nên đơn giản hơn rất nhiều.
Hàm SUMIF là gì?
SUMIF là hàm được giới thiệu lần đầu trong Excel 2007. Sự ra đời của hàm này đã mang lại thắc mắc của nhiều người rằng hàm SUMIF khác gì hàm SUM, tại sao phải dùng nó trong khi đã có hàm SUM.
SUMIF là hàm dùng để tính tổng giá trị thỏa mãn điều kiện nhất định. Hàm SUMIF sử dụng cho việc tính tổng các ô theo ngày, số liệu, văn bản khớp với điều kiện đã được cung cấp. Và hàm này hỗ trợ cho những phép tính logic (>, <, <>, =) và ký hiệu (*,?) để phù hợp cho từng phần.
Công thức hàm SUMIF: =SUMIF(range,criteria,sum_range)
Nếu bạn cần thao tác các giá trị xuất hiện trong đối số phạm vi (tức là trích xuất năm từ ngày tháng để sử dụng trong tiêu chí), hãy xem hàm SUMPRODUCT và / hoặc FILTER.
Ý nghĩa của hàm SUMIF trong Excel
SUMIF cho phép mở rộng khả năng của hàm SUM. Thay vì cho phép tính tổng trong một phạm vi nhất định, để tính tổng với SUMIF tất cả các ô bắt buộc phải thỏa mãn được những điều kiện mà người dùng đưa vào tham số criteria.
Hàm SUMIF tiện lợi hơn rất nhiều nếu như bạn muốn tính tổng doanh thu của một đơn vị, doanh số của một nhóm nhân viên, hoặc doanh thu trong khoảng thời gian nhất định hoặc là tổng lương theo điều kiện nào đó.
Cách dùng hàm SUMIF trong Excel (cơ bản đến nâng cao)
Hàm SUMIF nhiều điều kiện (SUMIF nâng cao)
Nếu bạn đang cần tính tổng các giá trị có nhiều điều kiện (là OR). Điều này nghĩa là có ít nhất một trong các điều kiện thì sẽ được đáp ứng? Giải pháp đơn giản nhất cho bạn đó là tính được tổng tất cả các kết quả được trả về bởi một số hàm SUMIF.
Tính tổng bằng hàm SUMIF nhiều điều kiện (cách cơ bản)
Bài toán về cách tính tổng số sản phẩm do Mike và John cung cấp:
=SUMIF (C2: C9, “Mike”, D2: D9) + SUMIF (C2: C9, “John”, D2: D9)
Như các bạn thấy, hàm SUMIF đầu tiên sẽ cho biết số lượng tương ứng với điều kiện là “Mike”, còn hàm SUMIF kia lại đưa về số tiền liên quan đến điều kiện “John” và sau đó cộng hai kết quả này lại.
Tính tổng bằng hàm SUMIF nhiều điều kiện (cách nâng cao)
Tính tổng các sản phẩm KTE với người cung cấp không phải là “JAMES”
Với cách tính đơn giản ở ví dụ trên, bạn sẽ cần tính tổng bằng hàm SUMIF 3 người cung cấp là JONE, SCARLET và MICHELS, nhưng ở đây chúng ta sẽ dùng công thức:
= SUMIFS (C2: C12, A2: A12, “KTE”, B2: B12, “<> James”)
- (C2: C12 là phạm vi ô cần tính tổng
- A2: A12, KTE là ô đầu tiên phạm vi tiêu chí và tiêu chí
- B2: B12, <> James, là phạm vi tiêu chí và tiêu chí thứ hai)
Sau đó nhấn phím Enter để nhận kết quả.
Hàm SUMIF kết hợp VLOOKUP (SUMIF nâng cao)
Ví dụ bài toán doanh thu dùng hàm SUMIF và VLOOKUP
Cùng với những bảng dữ liệu cần tìm đối tượng thì dữ liệu có điều kiện thì việc kết hợp giữa hàm SUMIF và Vlookup sẽ hỗ trợ tìm kiếm nhanh dữ liệu hơn, kết quả chính xác hơn. Để dễ hiểu hơn hãy quan sát ví dụ dưới đây.
Bảng doanh thu dưới đây sẽ bao gồm có ba bảng nhỏ với ba nội dung khác nhau. Bảng 1 là nhân viên kèm mã số tương ứng, Bảng 2 sẽ là mã số nhân viên và doanh số đạt được. Bảng 3 sẽ là bảng để điền kết quả.
Nội dung là nhập kết quả tên nhân viên và doanh số của tương ứng đạt được vào trong bảng 3. Bên cạnh đó có thể tra cứu thêm doanh số của từng nhân viên khác khi thay đổi họ tên. Do đó, bạn cần sử dụng đến hai hàm SUMIF và hàm Vlookup, để tính tổng doanh số của nhân viên với điều kiện cho trước.
- Bước 1: Chúng ta sẽ áp dụng công thức vào bảng.
Công thức sẽ là: =SUMIF(D:D,VLOOKUP(B12,A3:B7,2,FALSE),E:E)
- Bước 2: Bạn sẽ nhập công thức bên trên tại ô C12 ở Bảng 3, rồi điền tên nhân viên muốn tính tổng doanh thu tại ô B12. Ở đây mình sẽ tính tổng doanh thu của Phí Thanh Lan.
Trước tiên điều cần làm là đổi cột đó về lại định dạng Number, rồi điều chỉnh để hiện dấu phẩy phân cách ở trong ô Format Cells. Còn phần Decimal places có thể tùy chỉnh tùy theo số mà bạn thấy dễ tính toán.
- Bước 3: Bây giờ thì bạn đã có thể đổi tên của bất cứ nhân viên nào, không cần nhập công thức tính khác mà vẫn có kết quả chính xác.
Ví dụ mình sẽ nhập thông tin mã số nhân viên MS01 vào ô B12 tên nhân viên Trần Thu Hà, thì kết quả doanh thu cần tìm kiếm sẽ cho ra kết quả chính xác.
Hàm SUMIF kết hợp hàm AND (SUMIF nâng cao)
Giả sử như bạn hiện đang có một bảng liệt kê hàng trái cây từ nhiều nhà cung cấp. Và bạn có tên quả ở trong cột A, tên nhà cung cấp ở trong cột B và số lượng lại nằm trong cột C.
Giờ đây bạn muốn tìm được số tiền liên quan đến quả và nhà cung cấp, ví dụ: Tất cả trái táo được cung cấp bởi nhà cung ứng Pete. Để bắt đầu thì hãy xác định tất cả các đối số cho công thức SUMIFS:
- sum_range – C2: C9
- criteria_range1 – A2: A9
- criteria1 – “apples”
- criteria_range2 – B2: B9
- criteria2 – “Pete”
Bây giờ lại tất cả những thông số trên và bạn nhận được công thức cuối cùng như sau:
=SUMIFS(C2: C9, A2: A9, “táo”, B2: B9, “Pete”)
Để có thể khiến cho việc chỉnh sửa công thức đơn giản hơn, bạn có thể thay thế những tiêu chuẩn văn bản “apples” và “Pete” bằng những tham chiếu ô. Trong trường hợp bên dưới, bạn sẽ không cần phải thay đổi công thức để tính toán được lượng trái cây từ những nhà cung cấp khác nhau:
=SUMIFS(C2: C9, A2: A9, F1, B2: B9, F2).
Cách sử dụng Hàm SUMIF nhiều sheet
Cũng ở bài toán trên, làm thế nào để bạn có thể tìm được tổng số apples (táo) được bán ở tất cả các tiểu bang trong ba tháng vừa qua?
Như các bạn đã 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. Đó là lý do mà bạn không thể sử dụng công thức
=SUMIF(A2: A9, “apples”, C2: E9). Bởi vì nó sẽ tự thêm những giá trị tương ứng với “Apples” chỉ có ở trong cột C.
Giải pháp hợp lý và đơn giản nhất đó là nó tạo ra một cột để trợ giúp tính từng tổng số cho mỗi hàng và sau đó cột này sẽ được tham chiếu ở trong tiêu chí sum_range.
Đặt công thức SUM đơn giản bên trong ô F2, tiếp đến điền vào cột F: = SUM (C2: E2). Và sau đó, bạn có thể viết được một công thức SUMIF đơn giản:
=SUMIF(A2: A9, “apples”, F2: F9) hoặc là =SUMIF (A2: A9, H1, F2: F9)
Trong những công thức trên, sum_range có cùng kích thước với dải, tức là 1 cột và 8 hàng, và kết quả sẽ chính xác hơn. Nếu muốn làm mà không cần cột trợ giúp, thì có thể viết một công thức SUMIF riêng cho từng cột mà bạn muốn tính tổng. Sau đó thêm các kết quả về với hàm SUM như sau:
=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 một công thức mảng phức tạp hơn (nhưng cũng đừng quên 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 nào? Với dạng cơ bản chắc hẳn khá dễ để sử dụng, tuy nhiên trong phần nâng cao lại khó áp dụng hơn cả, vì vậy bạn cần vận dụng một cách từ tốn cho đến khi làm quen hoàn toàn. Chúc các bạn thành công với các thủ thuật excel trên.
