Hướng dẫn cách dùng hàm SUMIF, SUMIFS trong excel

Hàm sum, hàm sumif và hàm sumifs được sử dụng nhiều trong tính toán trên excel vì đây là những hàm tính tổng. Hàm Sumif được tạo ra để thực hiện yêu cầu tính toán với điều kiện để trích lọc dữ liệu trong bảng nhanh hơn. Vậy thì cách dùng hàm sumif như thế nào, chúng ta cùng tìm hiểu ngay sau đây. 

Hàm SUMIF trong Excel

Trong hàm excel, các tính toán đều được lập trình dựa trên công thức, như những công thức sum, if, countif, rank… Hàm SUMIF trong excel là hàm dùng để tính tổng các giá trị trong một phạm vi đáp ứng điều kiện nào đó. 

Ví dụ: Trong một cột chứa các số, bạn chỉ muốn tính tổng những giá trị lớn hơn 5. Bạn có thể sử dụng công thức sau: = SUMIF(B2:B25,”>5″). Trong đó điều kiện để áp dụng hàm Sumif là giá trị cần tìm phải lớn hơn 5.

Cách dùng hàm SUMIF

Cú pháp hàm SUMIF 

– Cú pháp hàm Sumif: = SUMIF(range, criteria, [sum_range])

– Hàm Sumif có 3 đối số và 2 đối số đầu là bắt buộc, còn đối số thứ 3 là tùy chọn.

– Trong đó: 

+ range – phạm vi (bắt buộc). Phạm vi ô bạn muốn được đánh giá theo tiêu chí. Các ô trong mỗi phạm vi phải là số hoặc tên, mảng hay tham chiếu chứa số. Giá trị trống và giá trị văn bản bị bỏ qua. Phạm vi được chọn có thể chứa các ngày ở định dạng Excel tiêu chuẩn (ví dụ bên dưới).

+ criteria – điều kiện (bắt buộc). Tiêu chí ở dạng số, biểu thức, tham chiếu ô, văn bản hoặc hàm xác định sẽ cộng các ô nào. Ký tự đại diện có thể được bao gồm dấu chấm hỏi (?) để khớp với bất kỳ ký tự đơn nào. Dấu hoa thị (*) để khớp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.

Ví dụ: tiêu chí có thể được thể hiện là 32, “>32”, B5, “3?”, “Apple *”, “* ~?”, hoặc hôm nay ().

Lưu ý: Mọi tiêu chí văn bản hoặc mọi tiêu chí bao gồm biểu tượng lô-gic hoặc toán học đều phải được đặt trong dấu ngoặc kép (“). Nếu tiêu chí ở dạng số, không cần dấu ngoặc kép.

+ sum_range – các ô tính tổng (tùy chọn). Các ô thực tế để cộng nếu bạn muốn cộng các ô không phải là các ô đã xác định 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). Và Sum_range phải có cùng kích cỡ và hình dạng theo phạm vi.

Ví dụ 

– Ví dụ: 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, sản phẩm ở ví dụ này là bananas.  

– Xác định các đối số cho công thức SUMIF của chúng ta:

+ range-A2: A8

+ criteria – “bananas”

+ sum_range – C2: C8

=> Công thức tính như sau: = SUMIF (A2: A8, “bananas”, C2: C8)

– Công thức trên thể hiện cách sử dụng đơn giản hàm SUMIF với các tiêu chí dạng văn bản (text). Bạn cũng 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.

– 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ũ. Song, cách làm tốt nhất để tránh sai sót vẫn là cung cấp range và sum_range có cùng kích thước với số dải.

Hàm SUMIF sử dụng dấu lớn hơn, nhỏ hơn hoặc bằng

– Vì công thức SUMIF để thỏa mãn điều kiện nên bạn hoàn toàn thêm các giá trị lớn hơn, nhỏ hơn hoặc bằng một giá trị nhất định vào trong công thức. 

– Lưu ý là đặt điều kiện trong dấu ngoặc kép (” “).

Hàm SUMIF sử dụng cho giá trị text

– Hàm SUMIF cũng cho phép bạn thêm các giá trị mà có sự phụ thuộc vào việc liệu một ô tương ứng trong một cột khác có thể chứa một văn bản cho trước hay không.

-Lưu ý: bạn sẽ cần các công thức 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 sau: 

– Hãy cùng xem ví dụ công thức chính xác “Tính tổng nếu khác”, nó cho biết lượng tồn kho của tất cả các sản phẩm khác ngoài “goldfinger bananas”:

– Công thức = SUMIF (A2: A7, “<> goldfinger bananas”, C2: C7)

Sử dụng hàm SUMIF để so sánh với tham chiếu ô

– Nếu bạn muốn sử dụng 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)

(điều kiện là tính tổng các giá trị khác F1). 

– Như vậy, bạn sẽ không phải thay đổi công thức để tính tổng có điều kiện dựa trên các tiêu chí khác mà bạn chỉ cần nhập một giá trị mới vào một ô được tham chiếu. Cách này cũng giống cách suy luận để đưa ra điều kiện.

– Lưu ý: 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 ngoặc kép (” “) để bắt đầu một chuỗi văn bản và dấu và (&) để nối.

– Ví dụ “<>” & F1.

– Dấu bằng (=) có thể được bỏ qua trước một tham chiếu ô, do đó cả hai công thức dưới đây là mang tính tương đương và chính xác như nhau: 

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

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

Hàm SUMIF với kí tự đại diện

– Nếu bạn tính tổng có điều kiện các ô dựa trên tiêu chí text và bạn muốn tính tổng bằng cách đối chiếu từng phần, thì bạn cần phải sử dụng các kí tự đại diện trong công thức SUMIF của bạn.

+ 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ể. 

VD1 – Các giá trị tính tổng dựa trên tham chiếu từng phần

Ví dụ, tính tổng số tiền liên quan đến tất cả các loại bananas. Thì các công thức SUMIF có dạng 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 ô bắt đầ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”.

VD2 – Tính tổng giá trị với số kí tự nhất định

Nếu bạn muốn tính tổng một số giá trị nếu giá trị này có chính xác 6 chữ cái, thì bạn sẽ sử dụng công thức sau: = SUMIF (A2: A8, “??????”, C2: C8)

VD3 – Tính tổng các ô chứa giá trị tương ứng giá trị văn bản

Nếu bảng tính chứa nhiều dạng dữ liệu mà bạn chỉ muốn tính dạng văn bản thì công thức SUMIF như sau: 

= 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ỏ qua các giá trị không phải là văn bản như các lỗi, các phép toán luận, các chữ số và ngày tháng.

VD4 – Sử dụng kí tự *, ? như kí tự bình thường

– Nếu muốn sử dụng  * hoặc ? như là một chữ chứ không phải là một kí tự đại diện thì hãy sử dụng dấu ngã (~) trước ký tự đó. 

– Ví dụ: công thức SUMIF sau 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 mà chứa một dấu chấm hỏi:

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

Hàm SUMIF tính tổng giá trị lớn nhất và giá trị nhỏ nhất trong một dải

Để tính tổng các số lớn nhất, nhỏ nhất trong dải/vùng dữ liệu thì chúng ta sử dụng hàm SUM cùng với hàm LARGE hoặc hàm SMALL.

VD1 – Thêm một vài số lớn nhất hoặc nhỏ nhất

– Nếu muốn tính tổng chỉ một số ít, chẳng hạn là 5 số thôi, thì bạ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

Lưu ý: Nếu có 2 hoặc nhiều hơn số được buộc cho vị trí cuối cùng, chỉ có số đầu tiên xuất hiện 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. Có nghĩa là chỉ một số chỉ xuất hiện một lần.

VD2 – Thêm vào giá trị lớn hoặc nhỏ

–  Và nếu bạn muốn thêm nhiều số mà không muốn phải liệt kê tất cả trong công thức thì bạn có thể sử dụng trong hàm SUM cùng với hàm ROW và hàm INDIRECT. 

– Trong hàm INDIRECT, hãy sử dụng số hàng đại diện cho số giá trị bạn muốn thêm vào. Ví dụ, các 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ì là các công thức mảng nên bạn hãy nhớ nhập chúng bằng cách nhấn tổ hợp phím Ctrl + Shift + Enter.

VD3 – Tổng một biến số trong các giá trị max, min

– Nếu bạn không muốn thay đổi công thức mỗi khi bạn muốn tính tổng một số ô khác nhau thì bạn có thể đặt số đó trong một ô nào đó thay vì nhập nó vào công thức. 

– Sau đó, bạn có thể sử dụng hàm ROW và INDIRECT và tham chiếu một ô chứa biến. 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àm SUMIF tính tổng các ô tương ứng với các ô trống

– Ô trống tức là các ô không chứa độ dài dữ liệu bằng 0, vậy nên excel cũng sẽ trả về chiều dài bằng 0. Khi đó bạn có thể sử dụng “=” làm tiêu chí, như trong công thức SUMIF sau:

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

Hoặc sử dụng công thức: 

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

Hàm SUMIF tính tổng các ô tương ứng với các ô không trống

– Các ô không trống có nghĩa là chứa dữ liệu và được nhận diện. Sử dụng “<>” làm tiêu chí trong công thức SUMIF: 

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

Xem thêm: Hướng dẫn sử dụng hàm sumif thông minh 

Hàm SUMIF với điều kiện ngày tháng

Hoặc có thể kết hợp với hàm TODAY như sau: 

Phân biệt hàm SUMIF VÀ SUMIFS

– Hàm Sumifs phức tạp hơn sumif, bởi vì đây là hàm tính tổng với nhiều điều kiện hơn.

– Cú pháp hàm Sumifs như sau: 

=SUMIFS(Sum_range, criteria range1, criteria1, criteria range2, criteria2,…, criteria range127, criteria127).

– Có thể thấy, cú pháp vùng tính được đưa lên đầu, và điều kiện vùng chọn nhiều hơn hàm Sumif. Như vậy nếu điều kiện càng nhiều thì phạm vi lấy càng thu hẹp. 

– Xem ví dụ dưới đây:

Vậy là qua bài viết, chúng ta đã biết cách sử dụng hàm Sumif một cách cơ bản nhất và cả những trường hợp yêu cầu suy luận điều kiện để sử dụng hàm. Hy vọng bài viết hỗ trợ các bạn trong quá trình học excel.