Cách dùng hàm sumif trong excel, có ví dụ và video chi tiết

Bạn đang vướng phải một bảng tính trong excel với nhiệm vụ tính tổng các dữ liệu thỏa mãn điều kiện, thì hàm Sumif là những gì bạn cần để thực hiện nhiệm vụ này. Bài viết dưới đây sẽ hướng dẫn bạn cách dùng hàm Sumif trong excel, có ví dụ và video chi tiết.

Hàm Sumif trong Excel – Cú pháp và ví dụ

Hàm Sumif, có nghĩa là tính tổng có điều kiện của Excel, được sử dụng để thêm các ô và dựa vào các điều kiện hoặc các tiêu chí nhất định để tìm ra.

Công thức như sau: SUMIF (range, criteria, [sum_range])

Như bạn thấy, trong hàm SUMIF có 3 tham số, trong đó có 2 tham số đầu là bắt buộc, còn tham số thứ 3 là tùy chọn.

  • range: Có nghĩa là dải của các ô được đánh giá theo tiêu chí của bạn, ví dụ A1: A20.
  • criteria: Có nghĩa là điều kiện/tiêu chí cần phải đáp ứng.
  • sum_range: Có nghĩa là các ô dùng để tính tổng

Hướng dẫn cách Sử Dụng Hàm SUMIF Trong EXCEL

SUMIF Lớn Hơn, Nhỏ Hơn Hoặc Bằng

Dưới đây là một số công thức SUMIF mà bạn hoàn toàn có thể sử dụng để so sánh các giá trị lớn hơn, hay nhỏ hơn hoặc bằng với giá trị được cho trước.

Cách dùng hàm sumif trong excel

Cách Sử Dụng Hàm SUMIF Với Điều Kiện Là Text

Hàm SUMIF cho phép bạn hoàn toàn có thể thêm các giá trị tùy thuộc vào ô tương ứng trong cột khác có chứa text hoặc không.

Cách Sử Dụng Hàm SUMIF Với Điều Kiện Là Text

Trong ví dụ dưới đây là yêu cầu tính tổng số tiền tất cả các mặt hàng không phải là “goldfinger bananas”:

=SUMIF(A2:A7,”<> goldfinger bananas”, C2:C7)

Cách Sử Dụng Hàm SUMIF Với Điều Kiện Là Text

Lưu ý: Cũng giống như các hàm trong Excel khác, hàm SUMIF này cũng không phân biệt chữ hoa và chữ thường.

Sử Dụng Toán Tử So Sánh Với Tham Chiếu Ô

Nếu muốn sử dụng công thức Excel SUMIF chung, bạn hoàn toàn có thể thay thế giá trị số hoặc text trong các điều kiện bằng tham chiếu ô:

=SUMIF(A2:A8,”<>”&F1, C2:C8)

Trong trường hợp này bạn hoàn toàn không cần thay đổi công thức để tính tổng giá trị dựa trên các tiêu chí khác, bạn chỉ cần nhập một giá trị mới vào ô được tham chiếu.

Sử Dụng Toán Tử So Sánh Với Tham Chiếu Ô

Lưu ý: Khi bạn sử dụng một biểu thức logic với tham chiếu ô, bạn bắt buộc phải sử dụng dấu ngoặc kép (“”) để bắt đầu chuỗi text và dấu và (&) để nối và kết thúc chuỗi, ví dụ “<>” & F1.

Không cần sử dụng toán tử “bằng” (=) trước một tham chiếu ô, vì vậy cả hai công thức dưới đây đều là công thức chính xác và cho kết quả tương đương nhau:

Công thức 1: =SUMIF(A2:A8, “=”&F1, C2:C8)

Công thức 2 là: =SUMIF(A2:A8, F1, C2:C8)

Công Thức SUMIF Với Ký Tự Đại Diện

Nếu mục đích sử dụng hàm Sumif của bạn là tính tổng các ô dựa trên các điều kiện “text” và muốn tổng hợp lại bằng cách đối sánh một phần, trường hợp này bạn bắt buộc phải sử dụng các ký tự đại diện trong công thức SUMIF.

Dưới đây là các ký tự có sẵn:

– Dấu hoa thị (*) – đại diện cho bất kỳ ký tự nào

– Dấu hỏi chấm (?) – đại diện cho một ký tự duy nhất tại một vị trí cụ thể

Ví dụ 1: Tính tổng giá trị dựa trên kết hợp từng phần

Giả sử nếu bạn muốn tính tổng số tiền của tất cả các mặt hàng là chuối, bạn phải sử dụng công thức SUMIF dưới đây:

=SUMIF(A2:A8, “*chuối*”, C2:C8) – điều kiện bắt buộc bao gồm phần text được đặt trong dấu hoa thị (*).

=SUMIF(A2:A8, “*”&F1&“*”, C2:C8) – điều kiện bắt buộc bao gồm các ô tham chiếu được đặt trong dấu hoa thị, sử dụng dấu (&) trước và sau một tham chiếu ô để nối chuỗi.

Nếu chỉ muốn đếm những ô bắt đầu hoặc kết thúc bằng một text cụ thể nào đó, chỉ cần thêm * vào trước hoặc sau text:

Cách dùng hàm sumif trong excel

=SUMIF(A2:A8, “chuối*”, C2:C8) – tổng giá trị trong dải ô C2:C8 nếu một ô tương ứng trong cột A bắt đầu bằng từ “chuối”.

=SUMIF(A2:A8, “*chuối”, C2:C8) – tổng giá trị trong dải ô C2:C8 nếu một ô tương ứng trong cột A kết thúc bằng từ “chuối”.

Mẹo: 

Sử dụng toán tử nối (&) để nối các ký tự đại diện với tham chiếu ô. Trong ví dụ trên, bạn hoàn toàn có thể sử dụng công thức sau để tính tổng các mặt hàng “chuối” trong kho:

=SUMIF(A2:A8, “*”&F1&”*”, C2:C8)

Ví dụ 2. Tính tổng giá trị với các ký tự nhất định

Trong trường hợp nếu bạn muốn tính tổng giá trị một số ký tự, chính xác là 6 ký tự, bạn phải sử dụng công thức dưới đây:

=SUMIF(A2:A8, “??????”, C2:C8)

Ví dụ 3. Tính tổng các ô tương ứng với các giá trị text

Nếu bảng tính của bạn chứa nhiều loại dữ liệu khác nhau, nhưng bạn chỉ muốn tính tổng các ô tương ứng giá trị text, bạn sử dụng công thức SUMIF dưới đây:

=SUMIF(A2:A8,“?*”, C2:C8) – cộng giá trị trong dải ô C2:C8 nếu một ô tương ứng trong cột A chứa ít nhất một biểu tượng.

=SUMIF(A2:A8,“*”,C2:C8) – bao gồm các ô trống, chứa các chuỗi độ dài bằng 0 được trả về bởi các công thức khác nhau, ví dụ: = “”.

Cả 2 công thức trên đều bỏ qua các giá trị không phải là text, chẳng hạn như các lỗi, hay các phép toán logic, hoặc số và ngày tháng.

Ví dụ 4: Sử dụng các ký tự * hoặc ? như các ký tự bình thường

Nếu muốn sử dụng các ký tự * hoặc ? giống như các ký tự thông thường chứ không phải các ký tự đại diện, chỉ cần thêm toán tử (~) trước đó.

Ví dụ: công thức SUMIF này sẽ thêm các giá trị trong dải ô C2:C8 nếu một ô trong cột A tương ứng và nằm trên cùng một hàng chứa dấu ?:

=SUMIF(A2:A8, “~?”, C2:C8)

=SUMIF(A2:A8, "~?", C2:C8)

Tính Tổng Giá Trị Lớn Nhất Hoặc Nhỏ Nhất Trong Một Vùng Dữ Liệu

Để tính tổng lớn nhất hoặc nhỏ nhất trong cùng một vùng, bạn sử dụng kết hợp hàm SUM với các hàm LARGE hoặc SMALL. 

Ví dụ 1: Thêm giá trị lớn nhất / giá trị nhỏ nhất

Nếu muốn tính tổng các giá trị lớn nhất, hoặc nhỏ nhất, chẳng hạn như 5, bạn hoàn toàn có thể nhập trực tiếp giá trị như trong công thức như dưới đây:

=SUM(LARGE(B1:B10,{1,2,3,4,5})) – tổng của 5 giá trị lớn nhất

=SUM(SMALL(B1:B10,{1,2,3,4,5})) – tổng của 5 giá trị nhỏ nhất

Cách dùng hàm sumif trong excel

Chú ý: Nếu có 2 hoặc nhiều giá trị giống nhau, thì chỉ có giá trị đầu tiên sẽ được tính. 

Ví dụ 2. Tính tổng giá trị trên / dưới 

Nếu bạn muốn tính tổng nhiều giá trị, thay vì liệt kê tất cả các giá trị này trong công thức, bạn hoàn toàn có thể lồng các hàm ROW và INDIRECT trong công thức SUM. Trong hàm INDIRECT, bạn sử dụng số hàng đại diện cho giá trị mà bạn muốn thêm.

Ví dụ các công thức tương ứng sau đây bạn tính tổng giá trị trên và dưới 15:

=SUM(LARGE(B1:B50,ROW(INDIRECT(“1:15”))))

=SUM(SMALL(B1:B50,ROW(INDIRECT(“1:15”))))

Vì đây là các công thức mảng, nhớ truy cập các mảng bằng cách sử dụng tổ hợp phím Ctrl + Shift + Enter.

Cách dùng hàm sumif trong excel

Cách Tính Tổng Các Ô Tương Ứng Với Các Ô Trống

Ô trống là các ô không chứa thông tin gì, không có công thức, không có chuỗi ký tự có độ dài bằng không, nó được trả về bởi một hàm Excel khác, bạn phải sử dụng “=” làm điều kiện, như trong công thức SUMIF dưới đây:

=SUMIF(A2:A10,”=”,C2:C10)

Nếu ô trống bao gồm các chuỗi thông tin có độ dài bằng không (ví dụ: các ô có công thức như = “”), sử dụng “” làm điều kiện:

=SUMIF(A2:A10,””,C2:C10)

Trong cả 2 công thức trên, bạn đánh giá các ô trong cột A và nếu phát hiện có bất kỳ ô trống nào, các giá trị tương ứng sẽ được thêm vào ở cột C.

Cách dùng hàm sumif trong excel

Cách Tính Tổng Các Ô Tương Ứng Với Các Ô Không Phải Trống

Nếu bạn muốn tính tổng giá trị các ô trong cột C khi mỗi một ô tương ứng trong cột A là ô trống, bạn sử dụng “<>” làm điều kiện trong công thức SUMIF:

=SUMIF(A2:A10,”<>”,C2:C10)

Công thức trên đếm tất cả các giá trị tương ứng với tất cả các ô không trống, trong đó bao gồm các chuỗi ký tự có độ dài bằng không.

Cách Sử Dụng Hàm SUMIF Với Điều Kiện Ngày Tháng

Về cơ bản, bạn sử dụng hàm SUMIF để tính tổng các giá trị dựa trên điều kiện ngày tháng giống như cách bạn sử dụng hàm SUMIF với các điều kiện chuẩn như text hoặc số.

Dưới đây là một vài ví dụ cụ thể về công thức Excel SUMIF:

 sử dụng hàm SUMIF để tính tổng các giá trị

Trường hợp nếu bạn muốn tính tổng các giá trị dựa trên ngày hiện tại, bạn sẽ phải sử dụng kết hợp hàm SUMIF với hàm TODAY() như ví dụ trong bảng dưới đây:

 sử dụng hàm SUMIF để tính tổng các giá trị

Kết quả của công thức cuối cùng để tính tổng số tiền các sản phẩm được ship trong tuần.

 sử dụng hàm SUMIF để tính tổng các giá trị

Cách Tính Tổng Giá Trị Trong Phạm Vi Ngày Cụ Thể

Bạn sử dụng kết hợp hoặc chính xác hơn là bạn sử dụng 2 hàm SUMIF khác nhau. Trong Excel phiên bản 2007 hoặc cao hơn, bạn hoàn toàn có thể sử dụng hàm SUMIFS cho nhiều điều kiện. 

Ví dụ cụ thể 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 SUMIF trên tính tổng giá trị trong các dải ô C2:C9 nếu ngày trong cột B nằm trong khoảng từ 1/10/2014 – 31/10/2014.

sử dụng hàm SUMIFS cho nhiều điều kiện

Hàm SUMIF đầu tiên sẽ tính giá trị các dải ô C2:C9 trong đó ô tương ứng trong cột B sẽ lớn hơn hoặc bằng ngày đầu tiên (trong ví dụ trên là ngày 1/10). Sau đó bạn trừ đi các giá trị rơi vào sau ngày cuối cùng (ngày 31/10) được trả kết quả về bởi hàm SUMIF thứ hai.

Xem thêm :   【Hướng Dẫn】Cách Set Ram Ảo Win 10 Dễ Dàng Nhất

We will be happy to hear your thoughts

Leave a reply

Wiki19.com
Logo
Enable registration in settings - general