Bạn đang xem bài viết Sự Khác Nhau Giữa Hàm Sumif Và Sumifs được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Trong Excel, có rất nhiều hàm tính tổng có điều kiện. Trong đó không thể không kể đến hai hàm SUMIF và SUMIFS. Chắc hẳn có những bạn còn đang băn khoăn về cách sử dụng, khi nào thì dùng hàm SUMIF, khi nào thì dùng hàm SUMIFS. Vậy hãy để chúng tôi giúp các bạn giải đáp thắc mắc đó trong bài viết này. Nội dung bài viết sẽ chia sẻ điểm khác biệt về hàm SUMIF và SUMIFS để các bạn nắm rõ, cũng như biết được cách sử dụng từng hàm, và không bị nhầm lẫn khi sử dụng. Bên cạnh kiến thức về lý thuyết, mình sẽ lấy những ví dụ minh họa đi kèm thực tế và dễ hiểu nhất.
Điểm khác biệt về hàm SUMIF và SUMIFS
Trong bài viết này mình sẽ nhắc lại kiến thức hai hàm một cách khái quát để các bạn nhớ lại, và tập chung vào những điểm khác biệt giữa hai hàm SUMIF và SUMIFS. Nếu muốn xem lại một cách đầy đủ hơn từng hàm các bạn hãy tham khảo bài viết chi tiết của blog.
Hàm SUMIF trong Excel
Hàm SUMIF là hàm tính tổng có điều kiện trong Excel, chỉ có một điều kiện nhất định.
=(range, criteria, [sum_range])
Trong đó:
Range: Mang tính bắt buộc, là phạm vi ô bạn muốn được đánh giá theo điều kiện được đưa ra. Các ô trong phạm vi phải ở dạng số hoặc mảng, tham chiếu chứa số. Nếu chứa giá trị trống hoặc văn bản bị bỏ qua.
Criteria: Mang tính bắt buộc là điều kiện cần phải thỏa mãn
Sum_range: Mang tính tùy chọn, là các ô được tính tổng nếu thỏa mãn điều kiện.
Nhìn vào cú pháp của hàm SUMIF các bạn thấy chỉ cho phép một điều kiện duy nhất.
Cho bảng số liệu, liệt kê các loại trái cây từ các nhà cung cấp khác nhau. Ở cột A, chúng ta có danh sách trái cây, ở cột B có tên nhà cung cấp tương ứng, số lượng từng loại quả ở cột C. Giả sử bạn muốn biết tổng Bananas (Chuối) tồn kho là bao nhiêu.
Dựa vào yêu cầu của đề bài đưa ra, chúng ta xác định các đối số
Range: Là cột sản phẩm A2:A9
Criteria: Là điều kiện ở ví dụ này chính là “Bananas”, do đã copy tên của điều kiện ra riêng, nên có thể nhập ô tham chiếu F1
Sum_range: Là cột số lượng
Sau đó chúng ta lắp ghép vào công thức hàm SUMIF =SUM(A2:A9,F1,C2:C9) và nhấn enter.
Ở ví dụ trên với một điều kiện, có thể sử dụng thay thế hàm SUMIF bằng hàm SUMIFS với cú pháp như bên dưới. Các đối số sẽ bị thay đổi vị trí.
Hàm SUMIFS trong Excel
Hàm SUMIFS là hàm được sử dụng trong trường hợp cần phải tính tổng khi có nhiều hơn một điều kiện trong excel.
Cú pháp của hàm SUMIFS phức tạp hơn so với cú pháp của hàm SUMIF
SUMIFS (sum_range, criteria_range1, criteria1, [criter_range2, criteria2], …)
sum_range: Là phạm vi một ô hoặc nhiều ô cần tính tổng.
Criteria_range1: Là phạm vi được kiểm tra bằng cách sử dụng điều kiện đầu tiên(Criteria1)
criteria_range2, criteria2, …: Đây là các phạm vi và điều kiện bổ sung, bạn được tùy chọn. Tối đa 127 cặp criteria_range, criteria có thể sử dụng trong hàm SUMIFS
Hàm SUMIFS hoạt động với biểu thức lô gic AND tức kết qủa trả về là tổng được tính khi được thỏa mãn đồng thời các điều kiện đó.
Hàm SUMIFS có thể dùng thay thế cho hàm SUMIF, nhưng hàm SUMIF thì không làm được bằng cú pháp thông thường.
Vẫn với ví dụ trên. Giả sử tất cả các loại Apples( Táo) do Pete cung cấp. Hãy tính tổng số lượng Apples đó.
Cách làm: Đầu tiên chúng ta xác định được điều kiện và các đối số để dựa vào đó lập công thức cho hàm.
sum_range : Chính là cột Qty (Số lượng), C2:C9
Criteria_range1: Là cột Product ( sản phẩm), A2:A9
Criteria1: Là “Apples”- F1 (dùng ô tham chiếu thay thế)
Criteria_range2: Là cột Supplier( nhà cung cấp), B2:B9
Criteria2: Là”Pete”- F2 (dùng ô tham chiếu thay thế)
Từ đó chúng ta xây dựng được công thức: = SUMIFS (C2: C9, A2: A9, F1, B2: B9, F2). Sau đó nhấn enter chúng ta có kết quả như bên dưới.
Đây là ví dụ về hàm SUMIFS sử dụng với hai điều kiện, nhiều hơn hai điều kiện các bạn thực hiện các bước làm tương tự.
Tổng kết những điểm khác biệt SUMIF và SUMIFS
Thông qua định nghĩa, công thức và cách sử dụng ở trên các bạn cũng thấy được điểm chung và điểm khác biệt cơ bản của hai hàm SUMIF và SUMIFS rồi. Nét chung đơn giản là đều để tính tổng có điều kiện, ngoài phụ thuộc vào số điều kiện thì hai hàm này còn những điểm khác nhau nào nữa. Các bạn cũng cần hiểu rõ để sử dụng hàm một cách chính xác.
Thứ tự của các đối số trong cú pháp
Hàm SUMIF và hàm SUMIFS trong Excel có trình tự các đối số không giống nhau: Sum_range là đối số đứng thứ ba trong công thức SUMIF, nhưng lại đứng thứ nhất trong công thức SUMIFS.
Nếu chỉ thoáng qua mà không tìm hiểu sâu, thì bạn sẽ nghĩ rằng Microsoft đang làm phức tạp hóa công thức hàm cho người học và người sử dụng nó. Nếu xem xét một cách kỹ lưỡng hơn bạn sẽ thấy ở hàm SUMIF thì sum_range là đối số tùy chọn, bạn có thể bỏ qua, không ảnh hưởng vẫn trả về kết quả. Ngược lại trong hàm SUMIFS, sum_range là đối số bắt buộc, và không thể lược bỏ, do đó tại sao nó được sắp xếp vị trí đứng đầu tiên ( vị trí này có lẽ sẽ là tốt hơn cho người dùng vì khó có thể quên đi đối số quan trọng này)
Vậy khi sử dụng hay sao chép và chỉnh sửa công thức của các hàm này, thì các bạn cũng cần chú ý đến thứ tự của các đối số để hàm trả về kết quả chính xác.
Kích thước của sum_range và criteria trong SUMIF và SUMIFS
– Đối với hàm SUMIF, đối số sum_range không cần phải có cùng kích thước với đối số range, miễn là bạn đảm bảo được rằng ô bên trái ở phía trên của đối số sum_range là trùng khớp.
– Còn với hàm SUMIFS, mỗi criteria_range bắt buộc phải chứa cùng một số hàng và cột như tham số sum_range.
Ví dụ. Với ví dụ 2, nếu sử dụng hàm SUMIF sẽ có công thức = SUMIF (A2: A9, F1, C2: C15) sẽ vẫn trả về kết quả đúng. Vì Excel chỉ dựa vào ô phía trên bên trái trong đối số sum_range (C2 là ) và tiếp theo là bao gồm số cột và hàng giống như kích thước của đối số range.
Khi sử dụng công thức SUMIFS: = SUMIFS (C2: C9, A2: A9, F1, B2: B11, F2) sẽ trả về giá trị lỗi #VALUE! Do criter_range2 (B2: B11) không cùng kích thước với (A2: A9)- criteria_range1 và (C2: C9)-sum_range .
Cảm ơn
Cách Sử Dụng Hàm Sumifs Và Sumif Với Nhiều Điều Kiện
Trong bài viết này, Học Excel Online sẽ giải thích sự khác biệt giữa các hàm SUMIF và SUMIFS theo cú pháp và cách sử dụng của chúng, bên cạnh đó, cũng cung cấp một số ví dụ về công thức để tính tổng các giá trị có nhiều điều kiện đồng thời xảy ra () hoặc tất cả không đồng thời xảy ra ( OR) trong phiên bản Excel 2013, 2010, 2007, 2003 và trước nữa.
range – dải của các ô được đánh giá theo điều kiện mà bạn đưa ra, mang tính bắt buộc.
criteria – điều kiện cần phải đáp ứng, mang tính bắt buộc
sum_range – các ô tính tổng nếu thỏa điều kiện, mang tính tùy chọn.
Như bạn thấy, cú pháp của hàm Excel SUMIF chỉ cho phép một điều kiện. Tuy nhiên, bên trên, chúng tôi có nói rằng Excel SUMIF có thể được sử dụng để tính tổng các giá trị với nhiều điều kiện. Làm thế nào mà có thể được? Thực tế, bạn cần thêm các kết quả của vài hàm SUMIF và sử dụng các công thức SUMIF với các điều kiện mảng, như trong ví dụ tiếp theo.
Hàm SUMIFS – cú pháp và cách sử dụng:
Bạn sử dụng SUMIFS trong Excel để tìm một tính tổng có giá trị các giá trị dựa trên nhiều điều kiện. Hàm SUMIFS đã được giới thiệu trong Excel 2007, vì vậy bạn có thể sử dụng nó trong tất cả các phiên bản của Excel 2013, 2010 và 2007.
So với SUMIF, thì cú pháp SUMIFS phức tạp hơn một chút:
sum_range – một hoặc nhiều ô tính tổng, mang tính bắt buộc. Đây có thể là một ô duy nhất, một dải ô hoặc dải có tên. Chỉ có các ô chỉ chứa số mới được tính tổng; Còn giá trị ô trống và giá trị văn bản thì sẽ bị bỏ qua.
Chú ý: Hàm SUMIFS hoạt động với biểu thức logic với AND, nghĩa là mỗi ô trong đối số sum_range chỉ được tính tổng nếu tất cả các điều kiện được chỉ định là đúng cho ô đó.
Bây giờ tập hợp các thông số trên, và bạn sẽ nhận được công thức SUMIFS sau:
= SUMIFS (C2: C9, A2: A9, F1, B2: B9, F2)
Sử dụng SUMIFS và SUMIF trong Excel – một số điều cần nhớ:
Mặc dù nét chung thì khá rõ ràng – tương đồng về điểm đến cho kết quả cuối cùng và các tham số – trong khi sự khác biệt dù không rõ rang nhưng vẫn rất cần thiết.
1. Trình tự của các đối số
Trong các hàm Excel SUMIF và SUMIFS, thứ tự các đối số là khác nhau. Cụ thể, sum_range là tham số thứ nhất trong SUMIFS, nhưng lại đứng thứ 3 trong công thức SUMIF.
Khi bắt đầu học, bạn sẽ cảm thấy có vẻ như Microsoft đã cố ý làm phức tạp hóa cho người học và người dùng nó. Tuy nhiên, khi xem xét kỹ hơn, bạn sẽ thấy lý do thực chất đằng sau nó. Vấn đề là sum_range là tùy chọn trong SUMIF. Nếu bạn bỏ qua nó, không có vấn đề, SUMIF công thức của bạn sẽ tính tổng các giá trị trong range (tham số đầu tiên).
Trong SUMIFS, sum_range là rất quan trọng và là bắt buộc, và đó là lý do tại sao nó đến trước. Có thể những người của Microsoft nghĩ rằng sau khi thêm các dải/điều kiện xem xét thứ 10 hoặc 100, thì có ai đó có thể quên xác định dải để tính tổng:)
Tóm lại, nếu bạn đang sao chép và chỉnh sửa các hàm này, hãy đảm bảo bạn đặt các thông số theo thứ tự đúng.
2. Kích thước của đối số sum_range và criteria_range
Trong hàm SUMIF, đối số sum_range không nhất thiết phải có cùng kích thước với đối số range, miễn là bạn có ô phía trên bên trái. Trong hàm SUMIFS, mỗi criteria_range phải chứa cùng một số hàng và cột như tham số sum_range.
Ví dụ, công thức = SUMIF (A2: A9, F1, C2: C18) sẽ trả lại kết quả đúng vì Excel chỉ xem ô phía trên bên trái trong đối số sum_range (C2 trong ví dụ này là đúng) và sau đó bao gồm số cột và hàng giống như kích thước của đối số range.
Công thức SUMIFS: = SUMIFS (C2: C9, A2: A9, “apples”, B2: B10, “Pete”) sẽ báo vì criter_range2 (B2: B10) không khớp với criteria_range1 (A2: A9) và sum_range (C2: C9).
Có vẻ như chúng ta đã tiếp cận tạm xong lý thuyết rồi, nên mục sau chúng ta sẽ chuyển sang thao tác thực hành (chính là các ví dụ công thức:)
Đăng ký ngay: Học Excel dành cho người đi làm
Làm thế nào để sử dụng SUMIFS trong Excel – ví dụ công thức
Ví dụ 1. Công thức SUMIFS với toán tử so sánh
Trong trường hợp bạn muốn tính tổng các giá trị với nhiều điều kiện dựa trên ngày hiện tại, hãy sử dụng hàm TODAY () trong điều kiện của hàm SUMIFS của bạn, như được trình bày bên dưới. Công thức sau đây tính tổng giá trị trong cột D nếu ngày tương ứng trong cột C rơi vào khoảng thời gian 7 ngày vừa qua, có bao gồm ngày hôm nay:
Tương tự như vậy, bạn có thể sử dụng hàm SUMIF để tính tổng các giá trị trong một dải có dữ liệu là ngày xác định. Ví dụ: công thức SUMIFS sau sẽ thêm các giá trị trong các ô C2: C9 nếu ngày trong cột B rơi trong khoảng giữa ngày 1 tháng 10 năm 2014 và ngày 31 tháng 10 năm 2014, như sau:
Kết quả tương tự có thể đạt được bằng cách tính toán sự khác biệt của hai hàm SUMIF, như được minh họa trong ví dụ này – Cách sử dụng SUMIF để tính tổng các giá trị trong một dải ngày xác định. Tuy nhiên, bạn cũng thấy rằng công thức SUMIFS là dễ dàng hơn và dễ hiểu hơn nhiều, phải không nào?
Hướng dẫn học Excel cơ bản
Ví dụ 3. Hàm SUMIFS với ô trống và không trống
Khi phân tích báo cáo và dữ liệu khác, bạn thường cần phải tính tổng các giá trị tương ứng với ô trống hoặc không trống.
Những ô trống
“=”
Tính tổng các giá trị mà có ô trống tương ứng (hoàn toàn không chứa dũ liệu – không công thức, và chuỗi có 0 kí tự)
=SUMIFS(C2:C10, A2:A10, “=”, B2:B10, “=”)
Tính tổng giá trị trong các ô C2:C10 nếu các ô tương ứng với nó trong cột A và B là ô hoàn toàn trống.
“”
Tính tổng các giá trị tương ứng với các ô trắng “nhận định trực quan”, bao gồm các giá trị chứa các chuỗi rỗng được trả về bởi một số hàm Excel khác (ví dụ: ô có công thức như = “”).
=SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”)
Tính tổng các giá trị trong các ô C2:C10 (có cùng các điều kiện) như công thức ở trên, nhưng có bao gồm các chuỗi trống.
Những ô không trống
Tính tổng các giá trị mà có các giá trị tương ứng là các ô không trống, và không bao gồm chuỗi có chiều dài bằng 0
Tính tổng giá trị trong các ô C2:C10 nếu các ô tương ứng với nó trong cột A và B không là ô trống, và không bao gồm các ô với chuỗi trống.
Giả sử bạn có ngày đặt hàng trong cột B, ngày giao hàng trong cột C và số lượng trong cột D. Làm thế nào để bạn tính được tổng số sản phẩm chưa được giao? Nghĩa là bạn muốn biết tổng các giá trị tương ứng với các ô không rỗng trong cột B và các ô rỗng trong cột C.
Giải pháp là sử dụng công thức SUMIFS với 2 điều kiện:
Như đã lưu ý ở phần đầu của bài này, thì hàm SUMIFS được thiết kế với AND. Nhưng nếu bạn cần tính tổng các giá trị có nhiều điều kiện OR, nghĩa là khi có ít nhất một trong các điều kiện sẽ được đáp ứng?
Ví dụ 1. SUMIF + SUMIF
Giải pháp đơn giản nhất là tính tổng các kết quả trả về bởi một số hàm SUMIF. Ví dụ: công thức sau đây sẽ minh họa 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)
Ví dụ 2. SUM & SUMIF với đối số mảng
Các giải pháp trên rất đơn giản và thực sự hiệu quả cao khi chỉ có một vài điều kiện. Nhưng một công thức SUMIF + SUMIF có thể được phát triển rất nhiều nếu bạn muốn tính tổng các giá trị với nhiều điều kiện OR. Trong trường hợp này, cách tiếp cận tốt hơn là sử dụng một đối số như điều kiện mảng trong hàm SUMIF, như sau:
Bạn có thể bắt đầu bằng cách liệt kê tất cả các điều kiện của bạn, được ngăn cách bởi dấu phẩy và sau đó đặt chúng trong {dấu ngoặc nhọn}, điều này được gọi là mảng.
Trong ví dụ trước, nếu bạn muốn tính tổng các sản phẩm do John, Mike và Pete cung cấp, điều kiện mảng của bạn sẽ như thế này {“John”, “Mike”, “Pete”}. Và hàm SUMIF hoàn chỉnh là: =SUMIF (C2: C9, {“John”, “Mike”, “Pete”}, D2: D9).
Đối số mảng bao gồm 3 giá trị bắt buộc phải có trong công thức SUMIF để trả về ba kết quả độc lập, nhưng vì chúng ta viết công thức trong một ô duy nhất, nó sẽ chỉ trả lại kết quả đầu tiên – tức là tổng số sản phẩm do John cung cấp. Để nó hoạt động, bạn phải sử dụng vài mẹo – lồng công thức SUMIF của bạn trong một hàm SUM, như sau:
= SUM (SUMIF (C2: C9, {“John”, “Mike”, “Pete”}, D2: D9))
Phương pháp này sẽ chạy với các con số hoặc các giá trị văn bản. Ví dụ: nếu thay vì bạn có tên các nhà cung cấp trong cột C, mà bạn chỉ có ID nhà cung cấp như 1, 2, 3, v.v … thì công thức SUMIF của bạn sẽ như sau:
= SUM (SUMIF (C2: C9, {1,2,3}, D2: D9))
Không giống như các giá trị văn bản, các con số không cần phải được bao gồm trong dấu nháy kép trong đối số mảng.
Trong trường hợp, Bạn thích liệt kê các điều kiện trong vài ô thay vì chỉ định chúng trực tiếp trong công thức, thì bạn có thể sử dụng SUMIF kết hợp với hàm SUMPRODUCT để nhân nhiều thành phần trong các mảng xác định rồi trả lại tổng số lượng của các sản phẩm đó.
Nhưng tất nhiên, không có gì ngăn cản nếu bạn muốn liệt kê các giá trị trong một điều kiện mảng của hàm SUMIF”
= SUMPRODUCT (SUMIF (C2: C9, {“Mike”, “John”, “Pete”}, D2: D9))
Kết quả trả về bởi cả hai công thức sẽ giống như những gì bạn thấy trong hình:
Như thường lệ, một ví dụ có thể giúp minh họa tốt hơn điều trên. Trong bảng các nhà cung cấp trái cây của chúng tôi, hãy thêm Delivery Date (Ngày giao hàng) (cột E) và tìm tổng số lượng được Mike, John và Pete giao vào tháng 10.
Công thức được tạo ra bởi cách phương pháp này bao gồm nhiều lần lặp đi lặp lại và trông có vẻ rườm rà, tuy vậy nhưng nó rất dễ hiểu, và điểm quan trọng nhất là nó hoạt động tốt 🙂
Tôi đã cố gắng giải thích bản chất của cách tiếp cận này trong ví dụ với SUMIF, vì vậy bây giờ chúng ta chỉ có thể sao chép công thức đó, thay đổi thứ tự các đối số (có sự khác nhau ở SUMIF và SUMIFS – như trên đã đề cập) và thêm các điều kiện bổ sung. Công thức kết quả sau sẽ nhỏ gọn hơn SUMIFS + SUMIFS:
Kết quả trả về bằng công thức này giống như những gì bạn thấy trong ảnh chụp màn hình ở trên.
Ví dụ 3. SUMPRODUCT & SUMIFS
Nếu như bạn nhớ, thì cách tiếp cận SUMPRODUCT khác với hai cách trước đó theo cách bạn nhập mỗi điều kiện của bạn vào một ô riêng biệt thay vì nhập chúng trực tiếp trong công thức. Trong trường hợp một số điều kiện, hàm SUMPRODUCT sẽ không đủ và bạn sẽ phải sử dụng hàm ISNUMBER và hàm MATCH.
Vì vậy, giả sử rằng các tên người cung cấp nằm trong các ô H1: H3, Ngày bắt đầu (Start date) nằm trong ô H4 và ngày kết thúc (End date) trong ô H5, công thức SUMPRODUCT của chúng tôi có dạng sau:
Tôi hy vọng lời giải thích trên có ý nghĩa với bạn. Và thậm chí nếu không, thì thực sự là bạn chỉ cần nhớ nguyên tắc này – sử dụng toán tử đôi (-) khi bạn sử dụng các toán tử so sánh trong các công thức SUMPRODUCT.
Sử dụng hàm SUM trong các công thức mảng
Như bạn đã biết, thì Microsoft đã thực hiện hàm SUMIFS trong Excel 2007. Nếu ai đó vẫn sử dụng Excel 2003, 2000 hoặc phiên bản trước đó, thì bạn sẽ phải sử dụng công thức mảng SUM để thêm các giá trị có nhiều điều kiện AND. Đương nhiên, phương pháp tiếp cận này cũng hoạt động trong các phiên bản hiện đại của Excel 2013 – 2007, và có thể được coi là sự lỗi thời của hàm SUMIFS.
Ví dụ 1. Sum với các điều kiện AND trong Excel 2003 và phiên bản trước đó:
Chúng ta hãy quay lại ví dụ đầu tiên mà chúng ta tính tổng số lượng ứng với một loại trái cây và nhà cung cấp nhất định:
= SUMIFS (C2: C9, A2: A9, “apples”, B2: B9, “Pete”)
Điều kiện 1: A2: A9 = “apples”
Điều kiện 2: B2: B9 = “Pete”
Sau đó, bạn viết một công thức SUM có nhân tất cả trong trường hợp: tương ứng với các cặp dải / điều kiện của bạn, mỗi cặp được đặt trong ngoặc đơn. Hệ số nhân cuối cùng là dải để tính tổng, là C2: C9 trong trường hợp này:
= SUM ((A2: A9 = “apples”) * (B2: B9 = “Pete”) * (C2: C9))
Như được minh họa trong hình bên dưới, thì công thức này hoạt động rất tốt trong phiên bản Excel 2013 mới nhất.
Ví dụ 2. Các công thức mảng SUM trong các phiên bản Excel hiện đại
Ngay cả trong các phiên bản hiện đại của Excel 2013, 2010 hoặc 2007, sức mạnh của hàm SUM không bao giờ bị đánh giá thấp. Công thức mảng SUM không chỉ đơn giản là luyện tập hướng suy nghĩ mà còn có giá trị thực tế, như trong ví dụ sau đây:
Giả sử bạn có hai cột, B và C, và bạn cần phải biết được cột C lớn hơn gấp bao nhiêu lần so với cột B, khi một giá trị trong cột C lớn hơn hoặc bằng 10. Một giải pháp mà bạn có thể nghĩ ra đo là sử dụng công thức mảng SUM:
Điều kiện 1: Giá trị trong cột B (Ordered items) lớn hơn 0
Điều kiện 2: Một giá trị trong cột C (Delivered) ít hơn trong cột B
Điều kiện 3: Ngày trong cột D (Due date) ít hơn 11/1/2014.
Đặt ba cặp range / điều kiện lại với nhau, bạn sẽ nhận được công thức sau:
Ngoài hàm SumIf, SumIfS giúp tính tổng theo nhiều điều kiện, chúng ta còn có thể sử dụng các hàm khác như SUMPRODUCT để làm việc này. Trong thực tế thì yêu cầu tính toán và đặc điểm dữ liệu sẽ quyết định tới việc chúng ta sẽ cần dùng hàm nào, dùng như thế nào. Do đó bạn cần bổ sung thêm cho mình càng nhiều kiến thức về hàm, về tổ chức dữ liệu thì càng thuận lợi khi làm việc.
Tiếp đó là việc cần phải kết hợp các công cụ của Excel để tăng hiệu quả công việc như: định dạng dữ liệu, định dạng theo điều kiện, trích lọc, sắp xếp, báo cáo bằng Pivot Table, vẽ biểu đồ…
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 Excel từ cơ bản đến chuyên gia dành cho người đi làm
Hàm Sumif Là Gì ? Cách Sử Dụng Hàm Sumif Trong Excel
Hàm SUMIF là gì ?
Trong Microsoft Excel , SUMIF là một công thức được sử dụng để cộng các giá trị lại với nhau trên một phạm vi ô. Hàm SUM cơ bản thêm các giá trị trong một phạm vi ô, bất kể giá trị là gì. Với SUMIF, người dùng có thể xác định các điều kiện cụ thể phải được đáp ứng cho các giá trị trong một phạm vi ô được đưa vào quy trình bổ sung.
Công thức và cú pháp hàm SUMIF
SUMIF( phạm vị, điều kiện, [sum_range] )phạm vi Phạm vi của các ô mà bạn muốn áp dụng các tiêu chí chống lại. tiêu chí Các tiêu chí được sử dụng để xác định các ô cần thêm. sum_range Không bắt buộc. Đây là phạm vi của các ô để tổng hợp lại với nhau. Nếu tham số này bị bỏ qua, nó sử dụng phạm vi là sum_range .
Hàm SUMIF trả về một giá trị số.
Áp dụng đối với
Excel cho Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 cho Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Cách sử dụng hàm SUMIF
Hãy nhìn vào hình ảnh bên phải. Nếu bạn chỉ muốn thêm các giá trị lớn hơn năm, bạn có thể viết công thức SUMIF như sau:
Vì các ô A2 và A5 chứa một giá trị lớn hơn năm, chúng sẽ là các ô duy nhất được cộng với nhau bởi công thức, dẫn đến giá trị là 17.
Ví dụ về hàm SUMIF
Sử dụng hàm sumif với con số
Ví dụ 1: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi A1: A5 nhỏ hơn hoặc bằng 10.
SUMIF(A1:A5,"<=10")Ví dụ 2: Hàm SUMIF sau đây cho kết quả chính xác như nhau. Nó sử dụng toán tử & để nối biểu tượng ‘nhỏ hơn hoặc bằng’ và giá trị trong ô C1.
SUMIF(A1:A5,"<="&C2)Ví dụ 3: Hàm SUMIF bên dưới (ba đối số, đối số cuối cùng là phạm vi cần tính tổng) tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa giá trị 25.
Ví dụ 4: Hàm SUMIF sau đây cho kết quả chính xác tương tự (đối số thứ hai đề cập đến ô D1).
Sử dụng hàm sumif với chữ văn bản
Ví dụ 5: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1:B5 nếu các ô tương ứng trong phạm vi A1:A5 chứa chính xác “circle”.
Ví dụ 6: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 không chứa chính xác “triangle”.
Ví dụ 7: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa chính xác “circle” + 1 ký tự. Một dấu hỏi (?) Khớp với chính xác một ký tự.
Ví dụ 8: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa một chuỗi gồm 0 hoặc nhiều ký tự + le. Dấu hoa thị (*) khớp với một loạt các ký tự bằng 0 hoặc nhiều hơn.
Ví dụ 9: Các hàm SUMIF bên dưới các giá trị tổng trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa chính xác “triangle” hoặc “circle8”.
Sử dụng hàm SUMIF thứ ngày tháng
Lưu ý: hàm DATE trong Excel chấp nhận ba đối số: năm, tháng và ngày.
Ví dụ 12: Hàm SUMIFS bên dưới (có chữ S ở cuối) tính tổng doanh số giữa hai ngày (bao gồm cả ngày bắt đầu và ngày kết thúc).
Lưu ý: hàm SUMIFS trong Excel tính tổng các giá trị dựa trên hai hoặc nhiều tiêu chí (đối số đầu tiên là phạm vi cần tính tổng, theo sau là hai hoặc nhiều cặp phạm vi / tiêu chí). Điều chỉnh ngày để tính tổng doanh số trong một tháng, năm cụ thể, v.v.
Hàm Sumif Là Gì, Ý Nghĩa Hàm Sumif, Dùng Sumif Trong Excel
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.
Công thức hàm SUMIF: =SUMIF(range,criteria,sum_range)
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)
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:
(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í
Sau đó nhấn phím Enter để nhận kết quả.
Hàm SUMIF kết hợp VLOOKUP (SUMIF nâng cao)
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.
sum_range – C2: C9
criteria_range1 – A2: A9
criteria1 – “apples”
criteria_range2 – B2: B9
criteria2 – “Pete”
=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ũ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.
Đặ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))
{= SUM ((C2: C9 + D2: D9 + E2: E9) * (- (A2: A9 = I1)))}
Cập nhật thông tin chi tiết về Sự Khác Nhau Giữa Hàm Sumif Và Sumifs trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!