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 thống kê giám sát trên excel vì đây là những hàm tính tổng. Hàm Sumif được tạo ra để thực thi nhu yếu giám sát với điều kiện kèm theo để trích lọc tài liệu trong bảng nhanh hơn. Vậy thì cách dùng hàm sumif như thế nào, tất cả chúng ta cùng khám phá ngay sau đây .

Hàm SUMIF trong Excel

Trong hàm excel, những giám sát đề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 những giá trị trong một khoanh vùng phạm vi phân phối điều kiện kèm theo nào đó .Ví dụ : Trong một cột chứa những số, bạn chỉ muốn tính tổng những giá trị lớn hơn 5. Bạn hoàn toàn có thể sử dụng công thức sau : = SUMIF ( B2 : B25, ” > 5 ″ ). Trong đó điều kiện kèm theo để vận 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 list những loại 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 toàn bộ những lượng hàng tồn dư tương quan đến một loại sản phẩm nhất định, loại sản phẩm ở ví dụ này là bananas .– Xác định 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=> Công thức tính như sau : = SUMIF ( A2 : A8, “ bananas ”, C2 : C8 )– Công thức trên biểu lộ cách sử dụng đơn thuần hàm SUMIF với những tiêu chuẩn dạng văn bản ( text ). Bạn cũng hoàn toàn có thể đưa vào 1 số ít, ngày hoặc một ô được tham chiếu đến trong tiêu chuẩn bạn đưa ra .– Ví dụ, bạn hoàn toà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 mẫu 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 lập ô 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 lập bởi size 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 cỡ với đối số dải, nghĩa là nó hoàn toàn có thể khác nhau về số lượng hàng và cột. Tuy nhiên, ô tiên phong ( 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 hoàn toàn có thể lấy ô C2, hoặc C2 : C4, hay thậm chí còn là C2 : C100 làm đối số sum_range, và tác dụng vẫn sẽ như cũ. Song, cách làm tốt nhất để tránh sai sót vẫn là cung ứng range và sum_range có cùng kích 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 nhu cầu điều kiện kèm theo nên bạn trọn vẹn thêm những 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 kèm theo trong dấu ngoặc kép ( ” “ ) .Tiêu chíDấu nốiCông thứcMô tảTính tổng nếu lớn hơn>= SUMIF ( A2 : A10, “ > 3 ” )Tính tổng những giá trị lớn hơn 3 trong những ô A2 : A10 .Tính tổng nếu nhỏ hơn<= SUMIF ( A2 : A10, “ < 9 ”, B2 : B10 )Tính tổng những giá trị trong những ô B2 : B10 nếu giá trị tương ứng trong cột A nhỏ hơn 9 .Tính tổng nếu bằng= ( hoàn toàn có thể bỏ lỡ )= SUMIF ( A2 : A10, “ = ” và E1 )hoặc= SUMIF ( A2 : A10, E1 )Tính tổng những giá trị trong những ô A2 : A10 mà có giá trị bằng với giá trị trong ô E1 .Tính tổng nếu khác< >= SUMIF ( A2 : A10, “ < > ” và E1, B2 : B10 )Tính tổng những giá trị trong những ô B2 : B10 nếu giá trị tương ứng trong cột A khác với giá trị trong ô E1 .Tính tổng nếu lớn hơn hoặc bằng> == SUMIF ( A2 : A10, “ > = 5 ” )Tính tổng những ô có giá trị lớn hơn hoặc bằng 5 trong dải 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 những giá trị trong những ô B2 : B10 nếu giá trị trong ô tương ứng trong cột A nhỏ hơn hoặc bằng 10 .

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

– Hàm SUMIF cũng được cho phép bạn thêm những giá trị mà có sự phụ thuộc vào vào việc liệu một ô tương ứng trong một cột khác hoàn toàn có thể chứa một văn bản cho trước hay không .- Lưu ý : bạn sẽ cần những công thức SUMIF khác nhau để so sánh đúng mực và từng phần, như được trình diễn trong bảng sau :Tiêu chíCông thứcMô tảTính tổng nếu bằngĐối chiếu trọn vẹn : = SUMIF ( A2 : A8, “ bananas ”, C2 : C8 )Giá trị tính tổng trong những ô C2 : C8 nếu ô tương ứng trong cột A chứa đúng mực từ “ bananas ” và không dư kí tự nào. Nên ô chứa “ green bananas ” hoặc “ bananas ! ” không được tính .Đối chiếu từng phần : = SUMIF ( A2 : A8, “ * bananas * ”, C2 : C8 )Giá trị tính tổng trong những ô C2 : C8 nếu ô tương ứng trong cột A chứa từ “ bananas ”, là một chuỗi kí tự trong * *. Vậy nên ô chứa “ green bananas ” hoặc “ bananas ! ” cũng được tính .Tính tổng nếu khácĐối chiếu trọn vẹn : = SUMIF ( A2 : A8, “ < > bananas ”, C2 : C8 )Giá trị tính tổng trong những ô C2 : C8 nếu ô tương ứng trong cột A chứa bất kể giá trị nào mà khác “ bananas ” .Đối chiếu từng phần : = SUMIF ( A2 : A8, “ < > * bananas * ”, C2 : C8 )Giá trị tính tổng trong những ô 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 chuẩn này .– Hãy cùng xem ví dụ công thức đúng mực “ Tính tổng nếu khác ”, nó cho biết lượng tồn dư của tổng thể những mẫu 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 hoàn toàn có thể thay thế sửa chữa giá trị số hoặc văn bản trong tiêu chuẩn bằng một ô tham chiếu, như sau := SUMIF ( A2 : A8, “ < > ” và F1, C2 : C8 )( điều kiện kèm theo là tính tổng những giá trị khác F1 ) .

– Như vậ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. Cách này cũng giống cách suy luận để đưa ra điều kiện kèm theo .

[adrotate banner=”3″]

ADVERTISEMENT– 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 ( ” “ ) để mở màn một chuỗi văn bản và dấu và ( và ) để nố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 mực như nhau :Công thức 1 : = SUMIF ( A2 : A8, “ = ” và 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 kèm theo những ô dựa trên tiêu chuẩn text 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 .+ Dấu hoa thị ( * ) – đại diện thay mặt cho nhiều kí tự .+ Dấu hỏi ( ? ) – đại diện thay mặt cho một kí tự duy nhất ở một vị trí đơn cử .

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 tương quan đến tổng thể những loại bananas. Thì những công thức SUMIF có dạng sau := SUMIF ( A2 : A8, “ bananas * ”, C2 : C8 ) – tiêu chuẩn gồm có những văn bản kết thúc với dấu hoa thị ( * ) .= SUMIF ( A2 : A8, “ * ” và F1 và “ * ”, C2 : C8 ) – những tiêu chuẩn gồm có một tham chiếu ô kèm theo dấu hoa thị, vui mắt quan tâm về việc sử dụng dấu và ( 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 mở màn 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ố í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 )

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 tài 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 những ô C2 : C8 nếu ô tương ứng trong cột A chứa tối thiểu 1 kí tự .= SUMIF ( A2 : A8, “ * ”, C2 : C8 ) – gồm có những ô có vẻ như là rỗng, chứa những chuỗi có độ dài bằng 0 ( là tác dụng của những 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 .

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

– Nếu muốn sử dụng * hoặc ? như thể 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 ký tự đó .– 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 )

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 những số lớn nhất, nhỏ nhất trong dải / vùng tài liệu thì tất cả 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 ít, ví dụ điển hình là 5 số thôi, 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

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ổng thể trong công thức thì bạn hoàn toà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 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ì 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 .

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

– Nếu bạn không muốn đổi khác công thức mỗi khi bạn muốn tính tổng một số ít ô khác nhau thì 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 .– Sau đó, bạn hoàn toà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 : ” và E1 ) ) ) ) – tính tổng những giá trị số 1 mà có chứa 1 ô đổi khác= SUM ( SMALL ( B1 : B50, ROW ( INDIRECT ( “ 1 : ” và E1 ) ) ) ) – tính tổng những giá trị nhỏ nhất mà có chứa 1 ô biến hóa .

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

– Ô trống tức là những ô không chứa độ dài tài 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 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 )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 tài liệu và được nhận diện. Sử dụng “ < > ” làm tiêu chuẩn trong công thức SUMIF := SUMIF ( A2 : A10, “ < > ”, C2 : C10 ) .Xem thêm : Hướng dẫn sử dụng hàm sumif mưu trí

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

Tiêu chíCông thứcMô tảTính tổng những 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 những ô C2 : C9 nếu ngày ở ô tương ứng trong cột B là ngày 29/10/2014Tính tổng những 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 những ô 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 những giá trị ô nếu ngày ở ô tương ứng lớn hơn ngày ở 1 ô khác .= SUMIF ( B2 : B9, ” > ” và F1, C2 : C9 )Tính tổng giá trị trong những ô C2 : C9 nếu ngày ở ô tương ứng trong cột B là sau ngày trong ô F1 .Hoặc hoàn toàn có thể tích hợp với hàm TODAY như sau :Tiêu chíCông thứcTính tổng những giá trị trong những ô dựa trên ngày hiện thời= SUMIF ( B2 : B9, TODAY ( ), C2 : C9 )Tính tổng những giá trị ô mà nằm trong quá khứ, có nghĩa là trước ngày hiện thời .= SUMIF ( B2 : B9, “ < “ và TODAY ( ), C2 : C9 )Tính tổng những giá trị ô mà nằm trong tương lai, có nghĩa là sau ngày hiện thời .= SUMIF ( B2 : B9, “ > ” và TODAY ( ), C2 : C9 )Tính tổng những giá trị ô nếu những ngày tương ứng đều được xảy ra ( có nghĩa là trong 7 ngày nữa ) .SUMIF ( B2 : B9, “ = ” và TODAY ( ) + 7, C2 : C9 )

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

– Hàm Sumifs phức tạp hơn sumif, do tại đây là hàm tính tổng với nhiều điều kiện kèm theo 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, tất cả 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 nhu yếu suy luận điều kiện kèm theo để sử dụng hàm. Hy vọng bài viết tương hỗ những bạn trong quy trình học excel .

Source: http://wp.ftn61.com
Category: Hỏi Đáp