Trong bài viết này, Học Excel Online sẽ giải thích hàm SUMIF và cung cấp một số ví dụ công thứ về hàm SUMIF cho dữ liệu dạng số, văn bản, ngày tháng và những kí tự đại diện.
Nếu bạn đang phải đối mặt với nhiệm vụ tính tổng các dữ liệu thỏa điều kiện trong Excel, thì hàm SUMIF là những gì mà bạn cần. Hướng dẫn này sẽ giải thích ngắn gọn về cú pháp của hàm và cách sử dụng chung, và tiếp đó bạn sẽ bổ sung thêm kiến thức trong việc thực hành với một số ví dụ công thức của hàm SUMIF.
- SUMIF trong Excel – cú pháp và cách sử dụng
- Làm thế nào để sử dụng Hàm SUMIF trong Excel – công thức minh họa
- SUMIF với dấu lớn hơn, nhỏ hơn hoặc bằng:
- Hàm SUMIF tính tổng theo điều kiện so sánh với chuỗi
- Sử dụng dấu so sánh với tham chiếu ô
- Công thức SUMIF với kí tự đại diện
- Ví dụ 1. Các giá trị tính tổng dựa trên đối chiếu từng phần:
- Ví dụ 2. Tổng giá trị với số kí tự nhất định
- Ví dụ 3. Tính tổng các ô có giá trị tương ứng với giá trị văn bản
- Ví dụ 4. Sử dụng * hoặc ? như những kí tự bình thường
- Tổng các số lớn nhất hoặc nhỏ nhất trong một dải:
- Ví dụ 1. Thêm một vài số lớn nhất / nhỏ nhất
- Ví dụ 2. Thêm vào các giá trị lớn/nhỏ
- Ví dụ 3. Tính tổng một biến số trong các giá trị lớn nhất / nhỏ nhất
- Làm thế nào để tính tổng các ô tương ứng với các ô trống
- Làm thế nào để tính tổng các ô tương ứng với các ô không trống
- Cách sử dụng Hàm SUMIF với ngày tháng
- Cách tính tổng các giá trị ứng với trong một quãng thời gian nhất định
- Làm thế nào để tính tổng các giá trị trong một vài cột
- Những câu hỏi thường gặp hoặc “Tại sao công thức SUMIF của tôi không hoạt động?”
- 1. Các tham số của range và sum_range phải là các dải, chứ không phải mảng:
- 2. Làm thế nào để tính tổng giá trị từ các trang tính hoặc bảng tính khác:
- 3. Hãy chắc chắn range và sum_range có cùng kích cỡ:
- 4. Làm thế nào để công thức SUMIF của bạn hoạt động nhanh hơn:
SUMIF trong Excel – cú pháp và cách sử dụng
Hàm SUMIF, còn được gọi là tính tổng có điều kiện của Excel, được sử dụng để thêm các ô, dựa trên một điều kiện hoặc tiêu chí nhất định.
Nếu bạn đã đọc hướng dẫn về hàm COUNTIF trên trang blog này, thì bạn sẽ không gặp bất kỳ khó khăn nào khi hiểu hàm SUMIF, bởi cú pháp và cách sử dụng của nó cũng tương tự. Do đó, cú pháp của hàm SUMIF như sau:
SUMIF [range, criteria, [sum_range]]
Như bạn thấy, hàm SUMIF có 3 đối số – 2 đối số đầu là bắt buộc, còn đối số thứ 3 là tùy chọn.
- range – dải của các ô được đánh giá theo tiêu chí của bạn, ví dụ A1: A10.
- criteria – điều kiện/tiêu chí cần phải đáp ứng. Các tiêu chí có thể được cung cấp dưới dạng số, văn bản, ngày, biểu thức logic, tham chiếu ô, hoặc một hàm Excel khác. Ví dụ: bạn có thể nhập các tiêu chí như “5”, “cherries“, “10/25/2014”, “
=SUMIF[A2:A10, “>5”] Tính tổng các số lớn hơn 5 trong vùng A2:A10 Tính tổng nếu nhỏ hơn < =SUMIF[A2:A10, “= =SUMIF[A2:A10, “>=5”] Tính tổng các số lớn hơn hoặc bằng 5 trong vùng A2:A10 Tính tổng nếu nhỏ hơn hoặc bằng ”&F1,C2:C9] Tính tổng giá trị trong các ô C2:C9 nếu ngày ở ô tương ứng trong cột B là sau ngày trong ô F1. Trong trường hợp bạn muốn tính tổng các giá trị dựa trên ngày hiện thời, thì bạn phải sử dụng hàm SUMIF kết hợp với hàm TODAY [], như sau:
Tiêu chí Công thức ví dụ Tính tổng các giá trị trong các ô dựa trên ngày hiện thời =SUMIF[B2:B9, TODAY[], C2:C9] Tính tổng các giá trị ô mà nằm trong quá khứ, có nghĩa là trước ngày hiện thời. =SUMIF[B2:B9, “”&TODAY[], C2:C9] Tính tổng các giá trị ô nếu các ngày tương ứng đều được xảy ra trong 1 tuần [có nghĩa là ngày hiện thời + 7 ngày nữa] =SUMIF[B2:B9, “=”&TODAY[]+7, C2:C9] Hình dưới đây minh hoạ cách bạn có thể sử dụng công thức cuối cùng để tìm tổng số tiền của tất cả các sản phẩm được chuyển đi trong một tuần.
Cách tính tổng các giá trị ứng với trong một quãng thời gian nhất định
Câu hỏi sau đây được hỏi thường xuyên trên các diễn đàn và blog của Excel – “Làm cách nào để tính tổng các giá trị ứng với thời gian nằm giữa hai ngày?”
Câu trả lời là sử dụng một sự kết hợp, hoặc chính xác hơn, là sự khác biệt giữa 2 công thức SUMIF. Trong Excel 2007 trở về bây giờ, bạn cũng có thể sử dụng hàm SUMIFS cho phép nhiều tiêu chí, thậm chí là một lựa chọn tốt hơn. Chúng tôi sẽ đề cập tới vế sau trong chủ đề của bài viết tiếp theo, còn dưới đây sẽ là ví dụ về công thức SUMIF:
= SUMIF [B2: B9, "> = 10/1/2014", C2: C9] - SUMIF [B2: B9, "> = 11/1/2014", C2: C9]Công thức này tính tổng các giá trị trong các ô C2: C9 nếu ngày trong cột B nằm giữa ngày 1/10/2014 và ngày 31/10/2014, bao gồm.
Công thức này có thể có vẻ hơi khó khăn nếu bạn chỉ lướt qua, nhưng khi nếu đi sâu vào nó, thì bạn sẽ thấy nó rất đơn giản. Hàm SUMIF đầu tiên đã thêm tất cả các ô trong C2: C9, nếu ô tương ứng trong cột B lớn hơn hoặc bằng ngày bắt đầu [trong ví dụ này là ngày 1 tháng 10]. Tiếp đó, bạn chỉ cần phải loại đi bất kỳ giá trị nào xảy ra vào sau ngày kết thúc [Oct-10 hay ngày 31/10] – là những giá trị được trả về bởi hàm SUMIF thứ hai.
Làm thế nào để tính tổng các giá trị trong một vài cột
Để hiểu vấn đề hơn, hãy xem xét ví dụ sau đây: Giả sử bạn có một bảng tóm tắt doanh thu hàng tháng và nó được thu thập từ một số báo cáo cho từng khu vực, trong đó có một vài hồ sơ cho cùng một sản phẩm ở trên:
Vì vậy, làm thế nào để bạn tìm thấy tổng số apples [táo] được bán ở tất cả các tiểu bang trong ba tháng qua?
Như 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 tại sao bạn không thể sử dụng công thức như = SUMIF [A2: A9, “apples”, C2: E9] bởi vì nó sẽ thêm các giá trị tương ứng với “Apples” mà chỉ ở trong cột C. Và đây không phải là những gì chúng ta đang tìm kiếm.
Giải pháp hợp lý và đơn giản nhất cho thấy chính nó là 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.
Đi trước và đặt công thức SUM đơn giản trong ô F2, sau đó điền vào cột F: = SUM [C2: E2]
Sau đó, bạn có thể viết một công thức SUMIF đơn giản như sau:
= SUMIF [A2: A9, "apples", F2: F9]hoặc là
= 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 dải, tức là 1 cột và 8 hàng, và do đó kết quả mà nó trả lại sẽ chính xác:
Nếu bạn muốn làm mà không có một cột trợ giúp, sau đó bạn có thể viết một công thức SUMIF riêng cho mỗi cột bạn muốn tính tổng, và sau đó thêm các kết quả trả về với 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 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]]]}}Cả hai công thức trên sẽ đưa ra kết quả là 2070 trong trường hợp trên.
Những câu hỏi thường gặp hoặc “Tại sao công thức SUMIF của tôi không hoạt động?”
Có thể có vài lý do khiến Hàm SUMIF không hoạt động trong tình huống của bạn. Đôi khi công thức của bạn sẽ không trả lại những gì bạn mong đợi chỉ vì kiểu dữ liệu trong một ô hoặc trong một số đối số không phù hợp với hàm SUMIF. Do đó, dưới đây là một danh sách những điều cần kiểm tra:
1. Các tham số của range và sum_range phải là các dải, chứ không phải mảng:
Các tham số đầu tiên [range] và thứ ba [sum_range] trong công thức SUMIF của bạn phải luôn là một tham chiếu dải, như A1: A10. Nếu bạn bất chấp mà bỏ qua điều này, ví dụ là trường hợp tham chiếu một mảng như {1,2,3}, thì Excel với trả lại với một thông báo lỗi.
Công thức đúng:
= SUMIF [A1: A3, "flower", C1: C3]Công thức sai:
= SUMIF [{1,2,3}, "flower", C1: C3]2. Làm thế nào để tính tổng giá trị từ các trang tính hoặc bảng tính khác:
Củng giống như hầu hết các hàm Excel khác, SUMIF có thể tham chiếu đến các trang tính và bảng tính khác, miễn là chúng vẫn đang mở.
Ví dụ: công thức sau đây sẽ tính tổng các giá trị trong các ô F2: F9 trong Sheet 1 [trang tính 1] của Book 1 [bảng tính 1] nếu có một ô tương ứng trong cột A và có trang tính đó cũng có chứa “apples”:
= SUMIF [[Book1.xlsx] Sheet1! $A$2:$A$9, "apples", [Book1.xlsx] Sheet1! $F$2:$F$9]Tuy nhiên, công thức này sẽ không hoạt động ngay khi Book 1 bị đóng lại. Điều này xảy ra vì các dải được tham chiếu bởi các công thức SUMIF trong bảng tính đã đóng, vậy nên nó sẽ không được tham chiếu đến các mảng. Mặt khác, cũng do không có mảng nào được cho phép trong range và đối số sum_range, nên sau đó, công thức SUMIF sẽ trả về một thông báo lỗi #VALUE!.
3. Hãy chắc chắn range và sum_range có cùng kích cỡ:
Như đã lưu ý ở phần đầu của hướng dẫn này, trong các phiên bản hiện đại của Microsoft Excel, các tham số range và sum_range không nhất thiết phải bằng nhau. Từ phiên bản Excel 2000 trở về trước, range và sum_range có kích thước không đồng đều có thể gây ra vấn đề. Tuy nhiên, ngay cả trong các phiên bản mới nhất của Excel 2010 và 2013, các công thức phức tạp SUMIF mà ở đó, nếu sum_range có ít hàng và/hoặc cột hơn range, thì sẽ không hợp lệ. Đó là lý do tại sao nó được coi là một minh họa tốt để người dùng chú ý rằng phải luôn luôn giữ đối số range và sum_range cùng kích thước và hình dạng.
4. Làm thế nào để công thức SUMIF của bạn hoạt động nhanh hơn:
Nếu bạn đã điền vào bảng tính với các công thức phức tạp SUMIF làm chậm Excel, hãy xem thử bài viết này và tìm hiểu Làm thế nào để chúng hoạt động nhanh hơn. Bài viết đã được viết cách đây khá lâu, vì vậy đừng ngạc nhiên bởi thời gian tính toán. Các ví dụ về Excel được trình bày trong hướng dẫn này chỉ liên quan đến một số cách sử dụng cơ bản của hàm này. Trong bài viết tiếp theo, chúng tôi sẽ đưa ra các công thức tiên tiến nhằm khai thác sức mạnh thực của SUMIF và SUMIFS và cho phép bạn tính tổng theo nhiều tiêu chí.
– – – – –
Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel.
Một số hàm cơ bản thường gặp như:
- SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện
- COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện
- Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…
- Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…
Một số công cụ hay sử dụng như:
- Định dạng theo điều kiện với Conditional formatting
- Thiết lập điều kiện nhập dữ liệu với Data Validation
- Cách đặt Name và sử dụng Name trong công thức
- Lập báo cáo với Pivot Table…
Rất nhiều kiến thức phải không nào? Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học.