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
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”, “<5”, “A1”, v.v …
Lưu ý : Hãy quan tâm rằng bất kể điều kiện kèm theo dạng văn bản hoặc điều kiện kèm theo có chứa những ký hiệu toán học thì đều phải được đặt trong dấu nháy kép ( “ ” ). Đối với những điều kiện kèm theo số, dấu nháy kép là không bắt buộc .
- sum_range – các ô dùng để tính tổng, nếu điều kiện được đáp ứng. Đối số này là tùy bạn chọn, và bạn chỉ cần sử dụng nó nếu bạn muốn tính tổng các ô khác (mà đã được định nghĩa trong đối số range). Nếu đối số sum_range bị bỏ qua, khi đó Excel sẽ tính tổng các ô tương tự mà các tiêu chí được đặt ra (nghĩa là chúng đã được chỉ định trong đối số range).
Để minh họa cú pháp hàm SUMIF một cách dễ hiểu, hãy xem xét ví dụ sau đây. Giả sử bạn có một danh sách các sản phẩm trong cột A và số lượng tương ứng trong kho trong cột C. Vấn đề đưa ra là bạn muốn biết tổng của tất cả các lượng hàng tồn kho liên quan đến một sản phẩm nhất định, ví dụ: bananas.
Bây giờ, hãy xác lập những đối số cho công thức SUMIF của tất cả chúng ta :
- range-A2: A8
- criteria – “bananas”
- sum_range – C2: C8
Gộp những đối số lại và bạn nhận được công thức : = SUMIF ( A2 : A8, “ bananas ”, C2 : C8 )
Công thức ví dụ này thể hiện cách sử dụng đơn giản của hàm SUMIF trong excel với các tiêu chí dạng văn bản. Thay vì văn bản, bạn có thể đưa vào một số, ngày hoặc một ô được tham chiếu đến trong tiêu chí bạn đưa ra. Ví dụ, bạn có thể viết lại công thức trên để nó tham chiếu đến ô có chứa tên của sản phẩm được tính tổng:
= SUMIF (A2: A8, F1, C2: C8)
Chú thích: Tham số sum_range thực sự chỉ xác định ô trên cùng bên trái của dải được tính tổng. Khu vực còn lại thì lại được xác định bởi kích thước của đối số range.
Trong thực tế, điều này có nghĩa là đối số sum_range không nhất thiết phải có cùng kích thước với đối số dải, nghĩa là nó có thể khác nhau về số lượng hàng và cột. Tuy nhiên, ô đầu tiên (tức là ô trên cùng bên trái) của dải tính tổng phải luôn luôn ở đúng cột. Ví dụ, trong công thức SUMIF ở trên, bạn có thể lấy ô C2, hoặc C2: C4, hay thậm chí là C2: C100 làm đối số sum_range, và kết quả vẫn sẽ như cũ. Tuy nhiên, hướng đi tốt nhất vẫn là cung cấp range và sum_range có cùng kích thước.
Làm thế nào để sử dụng Hàm SUMIF trong Excel – công thức minh họa
Hy vọng rằng, ví dụ trên đã giúp bạn hiểu cơ bản được cách hoạt động của hàm SUMIF. Dưới đây bạn sẽ tìm thấy một vài công thức cho biết rằng làm thế nào để sử dụng hàm SUMIF trong Excel với các tiêu chí khác nhau và loạt dữ liệu khác nhau.
SUMIF với dấu lớn hơn, nhỏ hơn hoặc bằng:
Hãy nhìn vào một số công thức hàm SUMIF mà bạn có thể sử dụng để thêm vào các giá trị lớn hơn, nhỏ hơn hoặc bằng một giá trị nhất định.
Lưu ý: Trong các công thức hàm SUMIF, một dấu so sánh theo sau bởi một số hoặc một văn bản phải luôn luôn được đặt trong dấu nháy kép (“”).
| Điều kiện |
Toán tử |
Công thức ví dụ |
Giải thích |
| Tính tổng nếu lớn hơn |
> |
=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, “<10”, B2:B10) |
Tính tổng các ô trong cột B, nếu các ô tương ứng, cùng dòng trong cột A có giá trị nhỏ hơn 10 |
| Tính tổng nếu bằng |
= |
=SUMIF(A2:A10, “=”&D1) hoặc =SUMIF(A2:A10,D1) |
Tính tổng các số trong vùng A2:A10, nếu bằng D1 |
| Tính tổng nếu không bằng |
<> |
=SUMIF(A2:A10, “<>”&D1, B2:B10) |
Tính tổng các ô trong cột B, nếu các ô tương ứng, cùng dòng trong cột A có giá trị khác giá trị ở D1 |
| Tính tổng nếu lớn hơn hoặc bằng |
>= |
=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 |
<= |
=SUMIF(A2:A10, “<=10”, B2:B10) |
Tính tổng các ô trong cột B, nếu các ô tương ứng, cùng dòng trong cột A có giá trị nhỏ hơn hoặc bằng 10 |
Hàm SUMIF tính tổng theo điều kiện so sánh với chuỗi
Hãy lưu ý rằng bạn sẽ cần các công thức hàm SUMIF khác nhau để đối chiếu chính xác và từng phần, như được trình bày trong bảng dưới đây.
| Tiêu chí |
Công thức ví dụ |
Mô tả |
| Tính tổng nếu bằng |
Đối chiếu hoàn toàn:
=SUMIF(A2:A8, “bananas”, C2:C8) |
Giá trị tính tổng trong các ô C2:C8 nếu ô tương ứng trong cột A chứa chính xác từ “bananas” và không dư một từ hay kí tự nào. Các ô chứa “green bananas”, “bananas green” or “bananas!” không bao gồm trong trường hợp này. |
Đối chiếu từng phần:
=SUMIF(A2:A8, “*bananas*”, C2:C8) |
Giá trị tính tổng trong các ô C2:C8 nếu ô tương ứng trong cột A chứa từ “bananas”, đứng độc lập hoặc đi kèm với những từ khác”. Các ô chứa “green bananas”, “bananas green” or “bananas!” sẽ được tính trong trường hợp này. |
| Tính tổng nếu khác |
Đối chiếu hoàn toàn:
=SUMIF(A2:A8, “<>bananas”, C2:C8) |
Giá trị tính tổng trong các ô C2:C8 nếu ô tương ứng trong cột A chứa bất kì giá trị nào mà khác “bananas”. Nếu ô chứa “bananas” có thêm 1 số từ hay kí tự khác, ví như “yellow bananas” hoặc “bananas yellow”, thì chúng sẽ được tính tổng. |
Đối chiếu từng phần:
=SUMIF(A2:A8, “<>*bananas*”, C2:C8) |
Giá trị tính tổng trong các ô C2:C8 nếu ô tương ứng trong cột A chứa bất kì giá trị nào mà khác “bananas”. Nếu ô chứa “bananas” có thêm 1 số từ khác. Các ô như “yellow bananas” or “bananas yellow” sẽ không thỏa tiêu chí này. |
Để biết thêm thông tin về giá trị khớp 1 phần, vui lòng xem ví dụ về hàm SUMIF trong excel với các kí tự đại diện.
Và giờ đây, hãy xem công thức đúng mực “ Tính tổng nếu khác ” qua những thao tác. Như được minh họa trong ảnh chụp màn hình hiển thị bên dưới, nó cho biết lượng tồn dư của toàn bộ những mẫu sản phẩm khác ngoài “ goldfinger bananas ” :
= SUMIF (A2: A7, "<> goldfinger bananas", C2: C7) 
Lưu ý: Giống như hầu hết các hàm Excel khác, SUMIF không phân biệt chữ hoa chữ thường, có nghĩa là dù bạn nhập “ Bananas” và “
Sử dụng dấu so sánh với tham chiếu ô
Nếu bạn muốn tiến xa hơn và muốn có một công thức hàm SUMIF phổ quát hơn, bạn có thể thay thế giá trị số hoặc văn bản trong tiêu chí bằng một ô tham chiếu, như sau:
= SUMIF (A2: A8, "<>" & F1, C2: C8)
Trong trường hợp này, bạn sẽ không phải đổi khác công thức để tính tổng có điều kiện kèm theo dựa trên những tiêu chuẩn khác – mà bạn chỉ cần nhập một giá trị mới vào một ô được tham chiếu .
Chú thích. Khi bạn sử dụng một biểu thức logic với một tham chiếu ô, bạn phải sử dụng dấu nháy kép ( “ ” ) để khởi đầu một chuỗi văn bản và dấu và ( và ) để nối / kết thúc chuỗi, ví dụ “ < > ” và F1 .
Dấu “ bằng ” ( = ) hoàn toàn có thể được bỏ lỡ trước một tham chiếu ô, do đó cả hai công thức dưới đây là mang tính tương tự và đúng chuẩn :
Công thức 1: = SUMIF (A2: A8, "=" & F1, C2: C8)
Công thức 2: = SUMIF (A2: A8, F1, C2: C8)
Công thức SUMIF với kí tự đại diện
Nếu bạn mục tiêu của bạn là việc tính tổng có điều kiện kèm theo những ô dựa trên tiêu chuẩn “ văn bản ” và bạn muốn tính tổng bằng cách so sánh từng phần, thì bạn cần phải sử dụng những kí tự đại diện thay mặt trong công thức SUMIF của bạn .
Hãy xem xét những kí tự đại diện thay mặt sau đây :
- Dấu hoa thị (*) – đại diện cho nhiều kí tự
- Dấu hỏi (?) – đại diện cho một kí tự duy nhất ở một vị trí cụ thể
Ví dụ 1. Các giá trị tính tổng dựa trên đối chiếu từng phần:
Giả sử, bạn muốn tính tổng số tiền tương quan đến tổng thể những loại chuối ( bananas ). Thì những công thức SUMIF sau sẽ chạy như sau :
= SUMIF (A2: A8, "bananas *", C2: C8)– tiêu chí bao gồm các văn bản kết thúc với dấu hoa thị (*).
= SUMIF (A2: A8, "*" & F1 & "*", C2: C8)– các tiêu chí bao gồm một tham chiếu ô kèm theo dấu hoa thị, vui lòng lưu ý về việc sử dụng dấu và (&) trước và sau một tham chiếu ô để nối với chuỗi.
Nếu bạn chỉ muốn tính tổng những ô khởi đầu hoặc kết thúc với một văn bản nhất định, thì bạn chỉ cần thêm một dấu * trước hoặc sau văn bản :
= SUMIF (A2: A8, "bananas*", C2: C8) – tổng giá trị trong C2: C8 nếu một ô tương ứng trong cột A bắt đầu bằng từ “bananas”.
= SUMIF (A2: A8, "*bananas", C2: C8) – tổng giá trị trong C2: C8 nếu một ô tương ứng trong cột A kết thúc bằng từ “bananas”.
Ví dụ 2. Tổng giá trị với số kí tự nhất định
Trong trường hợp bạn muốn tính tổng một số ít giá trị nếu giá trị này có đúng chuẩn 6 vần âm, thì bạn sẽ sử dụng công thức sau :
= SUMIF (A2: A8, "??????", C2: C8)
Ví dụ 3. Tính tổng các ô có giá trị tương ứng với giá trị văn bản
Nếu bảng tính của bạn chứa những loại tài liệu khác nhau và bạn chỉ muốn tổng những ô tương ứng với giá trị văn bản, thì những công thức SUMIF sau sẽ có ích cho bạn :
= SUMIF (A2: A8, "? *", C2: C8) – cộng giá trị trong các ô C2: C8 nếu ô tương ứng trong cột A chứa ít nhất 1 kí tự.
= SUMIF (A2: A8, "*", C2: C8) – bao gồm các ô có vẻ là rỗng, chứa các chuỗi có độ dài bằng 0 (là kết quả của các công thức khác), ví dụ: = “”.
Cả hai công thức trên đều bỏ lỡ những giá trị không phải là văn bản như những lỗi, những phép toán luận, những chữ số và ngày tháng .
Ví dụ 4. Sử dụng * hoặc ? như những kí tự bình thường
Nếu bạn muốn * hoặc ? được coi như là một chữ chứ không phải là một kí tự đại diện thay mặt, thì hãy sử dụng dấu ngã trước đó ( ~ ). Ví dụ : công thức SUMIF sau sẽ thêm những giá trị trong những ô C2 : C8 nếu một ô trong cột A nằm trong cùng một hàng mà chứa một dấu chấm hỏi :
= SUMIF (A2: A8, "~?", C2: C8)
Tổng các số lớn nhất hoặc nhỏ nhất trong một dải:
Để tính tổng các số lớn nhất hoặc nhỏ nhất trong dải, hãy sử dụng hàm SUM cùng với hàm LARGE hoặc SMALL.
Ví dụ 1. Thêm một vài số lớn nhất / nhỏ nhất
Nếu bạn muốn tính tổng chỉ một số ít ít, ví dụ như 5 số, thì bạn hoàn toàn có thể gõ chúng trực tiếp trong công thức, như sau :
= SUM (LARGE (B1: B10, {1,2,3,4,5})) – tổng 5 số lớn nhất
= SUM (SMALL (B1: B10, {1,2,3,4,5})) – tổng 5 số nhỏ nhất
Chú thích. Nếu có 2 hoặc nhiều hơn số được buộc cho vị trí sau cuối, chỉ có số tiên phong Open sẽ được tính. Như bạn thấy, số 9 thứ hai không được thêm vào trong ví dụ trên .
Ví dụ 2. Thêm vào các giá trị lớn/nhỏ
Nếu bạn muốn thêm nhiều số, thay vì liệt kê tổng thể trong công thức, bạn hoàn toàn có thể tổ chức triển khai những hàm ROW và hàm INDIRECT trong công thức SUM của bạn. Trong hàm INDIRECT, hãy sử dụng số hàng đại diện thay mặt cho số giá trị bạn muốn thêm vào. Ví dụ, những công thức sau đây tính tổng 15 số lớn nhất và nhỏ nhất, lần lượt là :
= SUM (LARGE (B1: B50, ROW (INDIRECT ("1:15"))))
= SUM (SMALL (B1: B50, ROW (INDIRECT ("1:15"))))
Vì đây là những công thức mảng, nên bạn hãy nhớ nhập chúng bằng cách nhấn tổng hợp phím Ctrl + Shift + Enter .
Ví dụ 3. Tính tổng một biến số trong các giá trị lớn nhất / nhỏ nhất
Nếu bạn không muốn biến hóa công thức mỗi khi bạn muốn tính tổng 1 số ít ô khác nhau, bạn hoàn toàn có thể đặt số đó trong một ô nào đó thay vì nhập nó vào công thức. Và sau đó, bạn hoàn toàn có thể sử dụng hàm ROW và INDIRECT như đã trình diễn ở trên và tham chiếu một ô chứa biến, như trong trường hợp này là ô E1 :
= SUM (LARGE (B1: B50, ROW (INDIRECT ("1:" & E1)))) – tính tổng các giá trị hàng đầu mà có chứa 1 ô thay đổi
= SUM (SMALL (B1: B50, ROW (INDIRECT ("1:" & E1)))) – tính tổng các giá trị nhỏ nhất mà có chứa 1 ô thay đổi
Hãy nhớ rằng, đây là những công thức mảng, thế cho nên bạn phải nhấn Ctrl + Shift + Enter để triển khai xong chúng .
Làm thế nào để tính tổng các ô tương ứng với các ô trống
Nếu “ trống ” thì có nghĩa là những ô đó trọn vẹn không có chứa gì – không có công thức, không có chuỗi chiều dài bằng 0 được trả lại bởi 1 số ít hàm Excel khác, khi đó bạn hoàn toàn có thể sử dụng “ = ” làm tiêu chuẩn, như trong công thức SUMIF sau :
= SUMIF (A2: A10, "=", C2: C10)
Nếu “ trống ” gồm có những chuỗi độ dài bằng không ( ví dụ : ô có công thức như = “ ” ), thì bạn hoàn toàn có thể sử dụng “ ” làm tiêu chuẩn :
= SUMIF (A2: A10, "", C2: C10)
Cả hai công thức trên đều nhìn nhận những ô trong cột A và nếu có bất kể ô trống nào được tìm thấy, những giá trị tương ứng từ cột C sẽ được thêm vào .
Làm thế nào để tính tổng các ô tương ứng với các ô không trống
Để tạo loại công thức “ nếu ô không trống thì tính tổng ”, hãy sử dụng “ < > ” làm tiêu chuẩn. Thao tác này sẽ cộng tổng thể những ô chứa bất kể thứ gì trong đó, gồm có cả những chuỗi có độ dài bằng không .
Ví dụ : đây là cách bạn hoàn toàn có thể tính tổng doanh thu bán hàng cho tổng thể những khu vực, tức là trong đó cột B không trống :
=SUMIF(B2:B10, “<>”, C2:D10)
Cách sử dụng Hàm SUMIF với ngày tháng
Nói chung, bạn sử dụng hàm SUMIF để tính tổng một cách có điều kiện kèm theo những giá trị dựa trên ngày cũng giống như cách bạn sử dụng văn bản và những tiêu chuẩn số .
Nếu bạn muốn tính tổng những giá trị nếu những giá trị tương ứng lớn hơn, nhỏ hơn hoặc bằng so với ngày bạn chỉ định, thì hãy sử dụng dấu so sánh mà chúng tôi đã đàm đạo từ những bài trước. Và dưới đây là một vài ví dụ công thức hàm SUMIF :
| Tiêu chí |
Công thức ví dụ |
Mô tả |
| Tính tổng các giá trị ô dựa trên 1 ngày tháng nhất định |
=SUMIF(B2:B9,”10/29/2014″,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à ngày 29/10/2014 |
| Tính tổng các giá trị ô nếu ngày ở ô tương ứng lớn hơn hoặc bằng với ngày đã cho |
=SUMIF(B2:B9,”>=10/29/2014″,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à từ ngày 29/10/2014 trở về sau (về hiện tại) |
| Tính tổng các gí trị ô nếu ngày ở ô tương ứng lớn hơn ngày ở 1 ô khác. |
=SUMIF(B2:B9,”>”&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 những giá trị dựa trên ngày hiện thời, thì bạn phải sử dụng hàm SUMIF tích 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ị ô mà nằm trong tương lai, có nghĩa là sau 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 hoàn toàn có thể sử dụng công thức ở đầu cuối để tìm tổng số tiền của tổng thể những loại 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 liên tục trên những forum và blog của Excel – “ Làm cách nào để tính tổng những giá trị ứng với thời hạn nằm giữa hai ngày ? ”
Câu vấn đáp là sử dụng một sự phối hợp, hoặc đúng mực hơn, là sự độc lạ giữa 2 công thức SUMIF. Trong Excel 2007 quay trở lại giờ đây, bạn cũng hoàn toàn có thể sử dụng hàm SUMIFS được cho phép nhiều tiêu chuẩn, thậm chí còn 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 những giá trị trong những ô 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, gồm có .
Công thức này hoàn toàn có thể có vẻ như hơi khó khăn vất vả 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 thuần. Hàm SUMIF tiên phong đã thêm toàn bộ những ô trong C2 : C9, nếu ô tương ứng trong cột B lớn hơn hoặc bằng ngày mở màn ( 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 yếu tố hơn, hãy xem xét ví dụ sau đây : Giả sử bạn có một bảng tóm tắt lệch giá hàng tháng và nó được tích lũy từ một số ít báo cáo giải trình cho từng khu vực, trong đó có một vài hồ sơ cho cùng một mẫu 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 ở toàn bộ những tiểu bang trong ba tháng qua ?
Như bạn đã biết, size của sum_range được xác lập bởi kích cỡ của tham số range. Đó là nguyên do tại sao bạn không hề sử dụng công thức như = SUMIF ( A2 : A9, “ apples ”, C2 : E9 ) chính bới nó sẽ thêm những giá trị tương ứng với “ Apples ” mà chỉ ở trong cột C. Và đây không phải là những gì tất cả chúng ta đang tìm kiếm .
Giải pháp hài hòa và hợp lý và đơn thuầ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 chuẩn sum_range .
Đi trước và đặt công thức SUM đơn thuần trong ô F2, sau đó điền vào cột F : = SUM ( C2 : E2 )
Sau đó, bạn hoàn toàn có thể viết một công thức SUMIF đơn thuần như sau :
= 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 cỡ với dải, tức là 1 cột và 8 hàng, và do đó hiệu quả mà nó trả lại sẽ đúng chuẩn :
Nếu bạn muốn làm mà không có một cột trợ giúp, sau đó bạn hoàn toà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 những hiệu 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 tác dụng 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 nguyên do khiến Hàm SUMIF không hoạt động giải trí trong trường hợp 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 tài liệu trong một ô hoặc trong 1 số ít đối số không tương thích với hàm SUMIF. Do đó, dưới đây là một list 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ố tiên phong ( 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 mặc kệ mà bỏ lỡ đ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 tin 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 những hàm Excel khác, SUMIF hoàn toàn có thể tham chiếu đến những 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 những giá trị trong những ô 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 những 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à khám phá Làm thế nào để chúng hoạt động giải trí nhanh hơn. Bài viết đã được viết cách đây khá lâu, thế cho nên đừng kinh ngạc bởi thời hạn giám sát. Các ví dụ về Excel được trình diễn trong hướng dẫn này chỉ tương quan đến 1 số ít 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 những công thức tiên tiến và phát triển nhằm mục đích khai thác sức mạnh thực của SUMIF và SUMIFS và được cho phép bạn tính tổng theo nhiều tiêu chuẩn .
– – – – –
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 kỹ năng và kiến thức phải không nào ? Toàn bộ những kỹ năng và kiến thức này những bạn đều hoàn toàn có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên viên của Học Excel Online. Đây là khóa học giúp bạn mạng lưới hệ thống kiến thức và kỹ năng một cách vừa đủ, cụ thể. Hơn nữa không hề có số lượng giới hạn về thời hạn học tập nên bạn hoàn toàn có thể tự do học bất kể khi nào, thuận tiện tra cứu lại kiến thức và kỹ năng khi cần. Hiện nay mạng lưới hệ thống đang có khuyến mại rất lớn cho bạn khi ĐK tham gia khóa học .
