Cách dùng hàm SUMIF trong Excel 2021 – công thức ví dụ dễ hiểu

nếu bạn đang phải đối mặt với nhiệm vụ tóm tắt dữ liệu trong excel, thì hàm sumif là những gì bạn cần. Hướng dẫn này giải thích ngắn gọn cú pháp và cách sử dụng chung của hàm, sau đó bổ sung vào kiến ​​thức làm việc của bạn một số công thức mẫu của hàm sumif .

sumif , còn được gọi là tổng có điều kiện trong excel, được sử dụng để tổng hợp 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 blog này, bạn sẽ không gặp khó khăn gì khi hiểu hàm sumif, vì cú pháp và cách sử dụng của nó tương tự nhau. vì vậy cú pháp hàm sumif như sau:

sumif (phạm vi, tiêu chí, [sum_range])

Như bạn có thể thấy, hàm sumif có 3 đối số: 2 đối số đầu tiên là bắt buộc và đối số thứ 3 là tùy chọn.

1. phạm vi : phạm vi ô được đánh giá theo tiêu chí của bạn, ví dụ: a1: a10.
2. tiêu chí : điều kiện / tiêu chí được gặp. tiêu chí có thể được cung cấp dưới dạng số, văn bản, ngày tháng, 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”, “& lt; 5”, “a1”, v.v …

lưu ý: Xin lưu ý rằng bất kỳ văn bản hoặc điều kiện nào có chứa các ký hiệu toán học phải được đặt trong dấu ngoặc kép (“”). đối với các điều kiện số, dấu ngoặc kép là tùy chọn.

1. sum_range : các ô tính tổng, nếu điều kiện được đáp ứng. đối số này là tùy chọn và bạn chỉ cần sử dụng nó nếu bạn muốn thêm các ô khác (được xác định trong đối số phạm vi). nếu đối số sum_range bị bỏ qua, excel sẽ tính tổng các ô giống nhau mà tiêu chí đã được đặt (nghĩa là chúng được chỉ định trong đối số dải ô).

Để minh họa cú pháp của hàm sumif một cách dễ hiểu, hãy xem xét ví dụ sau. Giả sử bạn có danh sách các sản phẩm ở cột a và số lượng tương ứng trong kho ở cột c. vấn đề là bạn muốn biết tổng số hàng tồn kho liên quan đến một sản phẩm nhất định, chẳng hạn như chuối.

Bây giờ, hãy xác định các đối số cho công thức sumif của chúng ta:

• range-a2: a8
• tiêu chí – “chuối”
• range_sum – c2: c8

kết hợp các đối số và bạn nhận được công thức: = sumif (a2: a8, “banana”, c2: c8)

Công thức ví dụ này minh họa cách sử dụng đơn giản của hàm sumif trong excel với tiêu chí văn bản. Thay vì văn bản, bạn có thể bao gồm một số, một ngày tháng hoặc một ô được tham chiếu trong tiêu chí bạn chỉ định. Ví dụ: bạn có thể viết lại công thức trên để tham chiếu đến ô chứa tên của sản phẩm đã thêm:

= sum if (a2: a8, f1, c2: c8)

lưu ý : tham số sum_range thực sự chỉ xác định ô trên cùng bên trái của phạm vi được tổng hợp. khu vực còn lại được xác định bởi kích thước của đối số phạm vi.

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 ô, có nghĩa là nó có thể khác nhau về số lượng hàng và cột. tuy nhiên, ô đầu tiên (nghĩa là ô trên cùng bên trái) của phạm vi tổng phải 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 hoặc thậm chí c2: c100 làm đối số sum_range và kết quả sẽ vẫn như cũ. tuy nhiên, cách tốt nhất vẫn là cung cấp range và sum_range có cùng kích thước.

Hy vọng rằng, ví dụ trên đã cho bạn hiểu cơ bản về cách hoạt động của hàm sumif. Dưới đây là một số công thức hướng dẫn cách sử dụng hàm sumif trong excel với các tiêu chí khác nhau và chuỗi dữ liệu khác nhau.

sumif với dấu lớn hơn, nhỏ hơn hoặc bằng:

Xem xét một số công thức hàm sumif mà bạn có thể sử dụng để tính tổng 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 của function your mif , dấu so sánh theo sau là số hoặc văn bản phải luôn được đặt trong dấu ngoặc kép. (“”).

hàm sumif tính tổng dựa trên điều kiện so sánh chuỗi

Lưu ý rằng bạn sẽ cần các công thức sumif khác nhau để đối sánh chính xác và từng phần, như được hiển thị trong bảng sau.

Để biết thêm thông tin về các đối sánh từng phần, hãy xem ví dụ về hàm sumif trong excel với các ký tự đại diện.

và bây giờ, hãy xem công thức chính xác của “tổng nếu khác” trong tất cả các phép toán. như thể hiện trong ảnh chụp màn hình bên dưới, nó hiển thị kho của tất cả các sản phẩm ngoài “chuối vàng”:

= 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à ngay cả khi bạn nhập “& lt; banana”, “& lt; & gt; banana” và “& lt; banana” thì chúng cũng cho kết quả giống nhau

sử dụng dấu hiệu so sánh với các tham chiếu ô

Nếu bạn muốn đi xa hơn và muốn có một công thức hàm sumif phổ biến 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::

= sum if (a2: a8, “& lt; & gt;” & f1, c2: c8)

Trong trường hợp này, bạn sẽ không phải thay đổi công thức để thêm có điều kiện 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 một ô được tham chiếu.

bình luận. khi bạn sử dụng biểu thức logic với tham chiếu ô, bạn phải sử dụng dấu ngoặc kép (“”) để bắt đầu chuỗi văn bản và dấu và (& amp;) để nối / kết thúc chuỗi, ví dụ: “& lt; & gt;” & amp; f1.

Xem thêm: Thì hiện tại đơn (Simple present) – Công thức, cách dùng, dấu hiệu

Có thể bỏ qua dấu bằng (=) trước tham chiếu ô, vì vậy hai công thức dưới đây là tương đương và chính xác:

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 mục tiêu của bạn là tính tổng các ô có điều kiện dựa trên tiêu chí “văn bản” và bạn muốn tính tổng bằng đối chiếu từng phần, thì bạn cần sử dụng các ký tự đại diện trong công thức sumif của mình.

hãy xem xét các ký tự đại diện sau:

• dấu hoa thị (*): biểu thị nhiều ký tự
• dấu chấm hỏi (?): biểu thị một ký tự duy nhất tại một vị trí cụ thể

ví dụ 1. tính tổng các giá trị dựa trên đối chiếu một phần:

Giả sử bạn muốn tính tổng số tiền liên quan đến tất cả các loại cây trồng (chuối). thì các công thức sumif sau sẽ được thực thi như thế này:

= sumif (a2: a8, “banana *”, c2: c8) – tiêu chí bao gồm văn bản kết thúc bằng dấu hoa thị (*).

= sumif (a2: a8, “*” & amp; f1 & amp; “*”, c2: c8) – tiêu chí bao gồm tham chiếu ô có dấu hoa thị, lưu ý việc sử dụng dấu và (& amp;) trước và sau ô tham chiếu đến các chuỗi nối.

Xem Thêm : Áp suất là gì? Đơn vị, công thức tính Áp suất khí như thế nào?

nếu bạn chỉ muốn tính tổng các ô bắt đầu hoặc kết thúc bằng một văn bản nhất định, chỉ cần thêm dấu * trước hoặc sau văn bản:

= sumif (a2: a8, “banana *”, c2: c8) – tổng các giá trị trong c2: c8 nếu một ô tương ứng trong cột a bắt đầu bằng từ ” chuối. ”

= sumif (a2: a8, “* banana”, c2: c8 ) – tổng các giá trị trong c2: c8 nếu một ô tương ứng trong cột a kết thúc bằng từ “ chuối ”.

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

Trong trường hợp bạn muốn thêm nhiều giá trị nếu giá trị có chính xác 6 chữ cái, thì bạn sẽ sử dụng công thức sau:

= sum if (a2: a8, “??????”, c2: c8)

ví dụ 3. thêm các ô có giá trị tương ứng với giá trị của văn bản

nếu bảng tính của bạn chứa các loại dữ liệu khác nhau và bạn chỉ muốn tính tổng các ô tương ứng với các giá trị văn bản, thì các công thức sumif sau sẽ phù hợp với bạn:

= sumif (a2: a8, “? *”, c2: c8) – thêm 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ẻ như trống, chứa các chuỗi có độ dài bằng 0 (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ỏ qua các giá trị không phải văn bản, chẳng hạn như lỗi, phép toán, chữ số và ngày tháng.

ví dụ 4. sử dụng * hoặc? như các ký tự bình thường

nếu bạn muốn * hoặc? được coi là một chữ thay vì một ký tự đại diện, hãy sử dụng dấu ngã đứng trước dấu ngã (~). ví dụ: công thức sumif sau đây sẽ thêm các giá trị trong các ô c2: c8 nếu một ô trong cột a nằm trong cùng một hàng có chứa dấu chấm hỏi:

= sum if (a2: a8, “~?”, c2: c8)

tổng các số lớn nhất hoặc nhỏ nhất trong một phạm vi:

Để thêm các số lớn nhất hoặc nhỏ nhất trong một phạm vi, hãy sử dụng hàm tổng cùng với lớn hoặc nhỏ.

ví dụ 1. thêm một số số lớn hơn / nhỏ hơn

nếu bạn chỉ muốn thêm một vài, chẳng hạn như 5 số, bạn có thể nhập chúng trực tiếp vào công thức, như sau:

= sum (big (b1: b10, {1,2,3,4,5})) – tổng của 5 số lớn nhất

= sum (small (b1: b10, {1,2,3,4,5})) – tổng của 5 số nhỏ nhất

bình luận. nếu 2 hoặc nhiều số được gắn cho vị trí cuối cùng, chỉ số đầu tiên xuất hiện mới được tính. Như bạn có thể thấy, số 9 thứ hai không được thêm vào trong ví dụ trên.

ví dụ 2. thêm các giá trị lớn / nhỏ

Nếu bạn muốn thêm nhiều số hơn, thay vì liệt kê tất cả chúng trong công thức, bạn có thể sắp xếp các hàm hàng và hàm gián tiếp trong công thức tính tổng của mình. trong hàm gián tiếp, hãy sử dụng số hàng đại diện cho số giá trị bạn muốn tính tổng. Ví dụ: các công thức sau đây thêm 15 số lớn nhất và nhỏ nhất, tương ứng:

= sum (lớn (b1: b50, hàng (gián tiếp (“1:15”))))

= sum (small (b1: b50, row (gián tiếp (“1:15”)))))

Xem thêm: Công thức tính giá trị trung bình thường gặp – Babelgraph

Vì đây là các công thức mảng, hãy đảm bảo bạn nhập chúng bằng cách nhấn ctrl + shift + enter.

ví dụ 3. thêm một biến trong các giá trị tối đa / tối thiểu

Nếu bạn không muốn thay đổi công thức mỗi khi bạn muốn thêm một số ô khác nhau, bạn có thể đặt số đó vào một ô thay vì nhập nó vào công thức. và sau đó, bạn có thể sử dụng các hàm hàng và hàm gợi ý như được hiển thị ở trên và tham chiếu đến một ô có chứa biến, như trong trường hợp này là ô e1:

= sum (large (b1: b50, row (gián tiếp (“1:” & amp; e1)))) – tính tổng các giá trị chính chứa 1 ô đã thay đổi

= sum (small (b1: b50, row (gián tiếp (“1:” & amp; e1)))) – tính tổng các giá trị nhỏ nhất chứa một ô được sửa đổi

hãy nhớ rằng đây là các công thức mảng, vì vậy bạn phải nhấn ctrl + shift + enter để hoàn thành chúng.

cách thêm ô tương ứng với ô trống

nếu “trống” có nghĩa là những ô đó hoàn toàn không chứa gì, không có công thức, không có chuỗi độ dài bằng 0 được trả về bởi một số hàm excel khác, thì bạn có thể sử dụng “=” làm tiêu chí, như trong công thức sumif sau:

= sum if (a2: a10, “=”, c2: c10)

nếu “trống” bao gồm các chuỗi có độ dài bằng 0 (ví dụ: các ô có công thức như = “”), bạn có thể sử dụng “” làm tiêu chí:

= sum if (a2: a10, “”, c2: c10)

cả hai công thức trên đều đánh giá các ô trong cột a và nếu tìm thấy các ô trống, các giá trị tương ứng trong cột c sẽ được thêm vào.

cách 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 “tổng nếu ô không trống”, hãy sử dụng “& lt; & gt;” làm tiêu chí. điều này sẽ tính tổng tất cả các ô có chứa nội dung nào đó, bao gồm cả các chuỗi có độ dài bằng không.

ví dụ: đây là cách bạn có thể tổng doanh số bán hàng của tất cả các khu vực, tức là cột b không trống:

= sumif (b2: b10, “& lt; & gt;”, c2: d10)

Cách sử dụng Hàm SUMIF với ngày tháng

Bạn thường sử dụng hàm sumif để tính tổng có điều kiện các giá trị dựa trên ngày giống như cách bạn sử dụng tiêu chí văn bản và số.

Nếu bạn muốn tính tổng các giá trị nếu các giá trị tương ứng lớn hơn, nhỏ hơn hoặc bằng ngày bạn chỉ định, hãy sử dụng dấu hiệu so sánh mà chúng ta đã thảo luận trong các bài viết trước. và đây là một số ví dụ về công thức sumif:

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 tại, bạn nên sử dụng hàm sumif kết hợp với hàm today (), như sau:

Xem Thêm : Hướng dẫn cách pha cồn ethanol 96% thành cồn sát khuẩn y tế đạt chuẩn

Hình dưới đây minh họa cách bạn có thể sử dụng công thức cuối cùng để tìm tổng số lượng của tất cả các sản phẩm được vận chuyển trong một tuần.

cách thêm giá trị cho một khoảng thời gian nhất định

Câu hỏi sau đây thường được hỏi trên các diễn đàn và blog excel: “Làm cách nào để thêm các giá trị cho khoảng thời gian giữa hai ngày?”

Câu trả lời là sử dụng kết hợp, hay chính xác hơn là sự khác biệt giữa 2 công thức sumif. trong excel 2007 và bây giờ bạn cũng có thể sử dụng hàm sumifs cho phép nhiều tiêu chí lựa chọn tốt hơn. chúng ta sẽ đề cập đến vấn đề thứ hai trong chủ đề của bài viết tiếp theo và đây là một ví dụ về công thức sumif:

= addf (b2: b9, “> = 10/1/2014”, c2: c9) – addf (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 trong khoảng từ ngày 1 tháng 10 năm 2014 đến ngày 31 tháng 10 năm 2014.

Công thức này có vẻ hơi khó nếu bạn chỉ đọc lướt qua, nhưng khi đã tìm hiểu kỹ, bạn sẽ thấy rằng 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). thì bạn chỉ cần loại bỏ bất kỳ giá trị nào xảy ra sau ngày kết thúc (ngày 10 tháng 10 hoặc ngày 31 tháng 10), các giá trị được trả về bởi hàm sumif thứ hai.

cách tính tổng các giá trị trong nhiều cột

Để hiểu rõ hơn vấn đề, hãy xem xét ví dụ sau: Giả sử bạn có bản tóm tắt doanh thu hàng tháng và bản tóm tắt doanh thu hàng tháng được thu thập từ nhiều báo cáo cho từng khu vực, bao gồm nhiều hồ sơ cho cùng một sản phẩm ở trên:

Xem thêm: Mẫu nguyển tử Bo và quang phổ của Hidro, trắc nghiệm vật lý lớp 12

vậy làm cách nào để tìm tổng số táo đã 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ố phạm vi. đó là lý do tại sao bạn không thể sử dụng công thức như = sumif (a2: a9, “táo”, c2: e9) vì nó sẽ thêm các giá trị tương ứng với “táo” chỉ nằm trong cột c. và đây không phải là những gì chúng tôi đang tìm kiếm.

Giải pháp đơn giản và hợp lý nhất được trình bày là tạo một cột để giúp tính tổng cho mỗi hàng và sau đó tham chiếu cột này trong tiêu chí sum_range.

hãy tiếp tục và đặt công thức tính tổng đơn giản vào ô f2, sau đó điền vào cột f: = sum (c2: e2)

thì bạn có thể viết một công thức sumif đơn giản như sau:

= sum if (a2: a9, “táo”, f2: f9)

hoặc

= sum if (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 ô, nghĩa là 1 cột và 8 hàng, vì vậy kết quả mà nó trả về sẽ đúng:

Nếu bạn muốn thực hiện mà không có cột trợ giúp, bạn có thể viết một công thức sumif riêng cho từng cột bạn muốn tính tổng, sau đó thêm các kết quả trả về bằng 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 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ẽ trả về 2070 trong trường hợp trên.

Có thể có một số lý do khiến hàm sumif có thể không hoạt động trong trường hợp của bạn. Đôi khi công thức của bạn sẽ không trả về những gì bạn mong đợi chỉ vì kiểu dữ liệu trong một ô hoặc một số đối số không khớp với hàm sumif. vì vậy đây là danh sách những thứ cần kiểm tra:

1. tham số dải ô và sum_range phải là dải ô, không phải là mảng:

Tham số đầu tiên (phạm vi) và tham số thứ ba (sum_range) trong công thức sumif của bạn phải luôn là tham chiếu phạm vi, chẳng hạn như a1: a10. nếu bạn bỏ qua điều này, chẳng hạn như trong trường hợp tham chiếu đến một mảng như {1,2,3}, excel sẽ trả về thông báo lỗi.

công thức đúng: = sumif (a1: a3, “flower”, c1: c3)

công thức không chính xác: = sumif ({1,2,3}, “flower”, c1: c3)

2. cách thêm giá trị từ bảng tính hoặc bảng tính khác:

Giống như hầu hết các hàm excel khác, sumif có thể tham chiếu các trang tính và bảng tính khác, miễn là chúng vẫn mở.

ví dụ: công thức sau sẽ tính tổng các giá trị trong các ô f2: f9 trong trang tính 1 (trang tính 1) của sổ làm việc 1 (trang tính 1) nếu có một ô tương ứng trong cột a và có thuộc tính trang là cũng chứa “táo”:

= sumif ([book1.xlsx] sheet1! $ a $ 2: $ a $ 9, “táo”, [book1.xlsx] sheet1! $ f $ 2: $ f $ 9)

Tuy nhiên, công thức này sẽ không hoạt động ngay khi đóng sổ làm việc 1. Điều này xảy ra vì công thức sumif phạm vi tham chiếu trong sổ làm việc đã đóng, do đó, mảng sẽ không được tham chiếu. mặt khác, vì mảng không được phép trong các đối số range và sum_range, công thức sumif sẽ trả về thông báo lỗi #value !.

3. đảm bảo rằng phạm vi và sum_range có cùng kích thướ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 giống nhau. từ excel 2000 trở về trước, dải ô và dải_ô có kích thước không bằng nhau có thể gây ra sự cố. 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 trong đó, nếu sum_range có ít hàng và / hoặc cột hơn phạm vi > strong>, nó 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 lưu ý rằng các đối số range và sum_range phải luôn có cùng kích thước và hình dạng.