Hàm sumif trong excel: cách sử dụng hàm sumif trong excel 2010

-
Excel mang lại Microsoft 365 Excel mang lại Microsoft 365 d&#x
E0;nh mang đến m&#x
E1;y Mac Excel cho web Excel 2021 Excel 2021 đến Mac Excel 2019 Excel 2019 cho Mac Excel năm nhâm thìn Excel năm nhâm thìn cho Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 coi th&#x
EA;m...Ẩn bớt

Bạn sử dụng hàm SUMIF để tính tổng các giá trị trong một phạm vi đáp ứng nhu cầu tiêu chí mà bạn xác định. Ví dụ: giả sử trong một cột chứa những số, các bạn chỉ ước ao tính tổng phần nhiều giá trị to hơn 5. Chúng ta cũng có thể sử dụng bí quyết sau: =SUMIF(B2:B25,">5")


*

Mẹo: 

Nếu muốn, chúng ta có thể áp dụng tiêu chí cho một phạm vi, rồi tính tổng những giá trị tương ứng trong một phạm vi khác. Ví dụ: phương pháp =SUMIF(B2:B5, "John", C2:C5) chỉ tính tổng những giá trị trong phạm vi C2:C5, trong số ấy các ô tương xứng ở phạm vi B2:B5 bởi "John".

Bạn đang xem: Cách sử dụng hàm sumif trong excel 2010


Quan trọng: Hàm SUMIF trả về công dụng không đúng mực khi các bạn dùng nó nhằm khớp những chuỗi dài hơn 255 cam kết tự hoặc với chuỗi #VALUE!.


Cú pháp

SUMIF(range, criteria, )

Cú pháp hàm SUMIF tất cả các đối số sau đây:

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 tuyệt 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 rất có thể chứa những ngày ở định dạng Excel tiêu chuẩn (ví dụ mặt dưới).

criteria 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. Cam kết tự đại diện hoàn toàn có thể được bao hàm - vệt chấm hỏi (?) để khớp với bất kỳ ký tự đối kháng nào, vệt sao (*) để khớp với ngẫu nhiên chuỗi ký tự nào. Nếu bạn muốn tìm một vệt chấm hỏi hay lốt sao thực sự, hãy gõ dấu bổ (~) trước cam kết tự.

Ví dụ: tiêu chí rất có thể được biểu thị là 32, ">32", B5, "3?", "apple*", "*~?", hoặc TODAY().


Quan trọng: 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 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 vào đối số range. Nếu đối số sum_range bị bỏ qua, Excel cộng các ô được xác định trong đối số range (chính các ô đã được áp dụng tiêu chí).

Sum_range nên tất cả cùng form size và dạng hình với dải ô. Nếu không, hiệu suất rất có thể bị ảnh hưởng và phương pháp sẽ tính tổng một phạm vi ô bắt đầu với ô thứ nhất trong sum_range nhưng có cùng size với phạm vi. Ví dụ:

phạm vi

sum_range

Các ô được xem tổng thực tế

A1:A5

B1:B5

B1:B5

A1:A5

B1:K5

B1:B5

Ví dụ

Ví dụ 1

Sao chép dữ liệu ví dụ vào bảng tiếp sau đây và dính vào ô A1 của một bảng tính Excel mới. Để những công thức hiển thị kết quả, hãy chọn chúng, dìm F2 và kế tiếp nhấn Enter. Trường hợp cần, chúng ta có thể điều chỉnh độ rộng lớn cột nhằm xem tất cả dữ liệu.

Giá trị bất động sản

Tiền hoa hồng

Dữ liệu

$100.000

$7,000

$250,000

$200,000

$14,000

$300,000

$21,000

$400,000

$28,000

Công thức

Mô tả

Kết quả

=SUMIF(A2:A5,">160000",B2:B5)

Tổng tiền hoa hồng cho những giá trị tài sản to hơn $160.000.

$63,000

=SUMIF(A2:A5,">160000")

Tổng các giá trị tài sản to hơn $160.000.

$900,000

=SUMIF(A2:A5,300000,B2:B5)

Tổng chi phí hoa hồng cho những giá trị gia sản bằng $300.000.

$21,000

=SUMIF(A2:A5,">" và C2,B2:B5)

Tổng chi phí hoa hồng cho các giá trị tài sản lớn hơn giá trị trên C2.

$49,000

Ví dụ 2

Sao chép dữ liệu ví dụ trong bảng dưới đây và dính vào ô A1 của một bảng tính Excel mới. Để những công thức hiển thị kết quả, nên chọn lựa chúng, nhấn F2 và sau đó nhấn Enter. Trường hợp cần, bạn có thể điều chỉnh độ rộng lớn cột nhằm xem tất cả dữ liệu.

Nhóm

Thực phẩm

Doanh số

Rau

Cà chua

$2,300

Rau

Cần tây

$5,500

Trái cây

Cam

$800

$400

Rau

Cà rốt

$4,200

Trái cây

Táo

$1,200

Công thức

Mô tả

Kết quả

=SUMIF(A2:A7,"Trái cây",C2:C7)

Tổng lợi nhuận của toàn cục thực phẩm trong team "Trái cây".

$2,000

=SUMIF(A2:A7,"Rau",C2:C7)

Tổng doanh thu của toàn bộ thực phẩm trong đội "Rau".

$12,000

=SUMIF(B2:B7,"*es",C2:C7)

Tổng doanh thu của cục bộ thực phẩm tất cả tên xong là "es" (Cà chua, Cam và Táo).

Trong nội dung bài viết này, Học Excel Online sẽ phân tích và lý giải hàm SUMIF và cung ứng một số ví dụ như công đồ vật về hàm  SUMIF cho tài liệu dạng số, văn bản, tháng ngày và rất nhiều kí tự đại diện.

Nếu bạn đang phải đối mặt với trách nhiệm tính tổng các dữ liệu thỏa điều kiện trong Excel, thì hàm SUMIF là các thứ mà bạn cần. Chỉ dẫn này sẽ giải thích ngắn gọn về cú pháp của hàm cùng cách sử dụng chung, cùng tiếp đó các bạn sẽ bổ sung thêm kỹ năng trong việc thực hành với một số trong những ví dụ cách làm của hàm SUMIF.


Làm cầm cố nào để sử dụng Hàm SUMIF trong Excel – công thức minh họa
Công thức SUMIF với kí trường đoản cú đại diện
Tổng các số lớn số 1 hoặc nhỏ tuổi nhất vào một dải:Những câu hỏi thường chạm mặt hoặc “Tại sao công thức SUMIF của tôi không hoạt động?”
SUMIF trong Excel – cú pháp và phương pháp sử dụngHàm SUMIF, có cách gọi khác 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 chuẩn nhất định.

Nếu các bạn đã đọc lí giải về hàm COUNTIF trên trang vabishonglam.edu.vn này, thì bạn sẽ không gặp bất kỳ khó khăn nào khi gọi hàm SUMIF, vì cú pháp và cách sử dụng của nó cũng tương tự. Vì chưng đó, cú pháp của hàm SUMIF như sau:

SUMIF (range, criteria, )

Như các bạn thấy, hàm SUMIF bao gồm 3 đối số – 2 đối số đầu là bắt buộc, còn đối số đồ vật 3 là tùy chọn.

Lưu ý: Hãy chú ý rằng bất kỳ điều khiếu nại dạng văn bản hoặc đk có chứa các ký hiệu toán học tập thì đều đề nghị được đặt trong lốt nháy kép (“”). Đối với những điều khiếu nại số, lốt nháy kép là ko bắt buộc.


*

*

sum_range – những ô dùng để tính tổng, nếu đk được đáp ứng. Đối số này là tùy chúng ta chọn, cùng bạn chỉ việc sử dụng nó nếu bạn có nhu cầu tính tổng các ô khác (mà đang được có mang trong đối số range). Ví như đối số sum_range bị bỏ qua, khi ấy Excel công thêm tổng các ô tương tự mà các tiêu chuẩn được đưa ra (nghĩa là bọn chúng đã được chỉ định trong đối số range).

Để minh họa cú pháp hàm SUMIF một biện pháp dễ hiểu, hãy chu đáo ví dụ sau đây. Mang sử bạn có một list các sản phẩm trong cột A và con số tương ứng vào kho vào cột C. Sự việc đưa ra là bạn có nhu cầu biết tổng của tất cả các lượng sản phẩm tồn kho tương quan đến một thành phầm nhất định, ví dụ: bananas.

*

Bây giờ, hãy xác định các đối số cho bí quyết SUMIF của chúng ta:

range-A2: A8criteria – “bananas”sum_range – C2: C8

Gộp các đối số lại và chúng ta nhận được công thức: = SUMIF (A2: A8, “bananas”, C2: C8)

*

Công thức lấy một ví dụ này thể hiện phương pháp sử dụng đơn giản dễ dàng của hàm SUMIF vào excel với các tiêu chí dạng văn bản. Thay vì văn bản, chúng ta cũng có thể đưa vào trong 1 số, ngày hoặc một ô được tham chiếu mang lại trong tiêu chuẩn bạn gửi ra. Ví dụ, chúng ta có thể viết lại bí quyết trên để nó tham chiếu mang lại ô tất cả chứa thương hiệu 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ỉ khẳng định ô trên cùng bên trái của dải được xem tổng. Quanh vùng còn lại thì lại được khẳng định bởi kích cỡ của đối số range.

Trong thực tế, điều này có nghĩa là đối số sum_range không độc nhất thiết phải có cùng form size với đối số dải, tức thị nó rất có thể khác nhau về con số hàng với cột. Mặc dù nhiên, ô thứ nhất (tức là ô trên cùng mặt trái) của dải tính tổng phải luôn luôn luôn nghỉ ngơi đúng cột. Ví dụ, trong công thức SUMIF ngơi nghỉ trên, bạn có thể lấy ô C2, hoặc C2: C4, hay thậm chí còn là C2: C100 có tác dụng đối số sum_range, và hiệu quả vẫn đã như cũ. Mặc dù nhiên, phía đi tốt nhất vẫn là cung ứng range cùng sum_range có thuộc kích thước.

Làm nuốm nào để áp dụng Hàm SUMIF vào Excel – cách làm minh họa

Hy vọng rằng, lấy một ví dụ trên đã giúp đỡ bạn hiểu cơ bản được cách hoạt động của hàm SUMIF. Bên dưới đây các bạn sẽ tìm thấy một vài ba công thức cho thấy rằng làm nỗ lực nào nhằm sử dụng hàm SUMIF trong Excel với những tiêu chí khác biệt và loạt dữ liệu khác nhau.

SUMIF cùng với dấu bự hơn, nhỏ dại hơn hoặc bằng:

Hãy nhìn vào một trong những công thức hàm SUMIF mà bạn cũng có thể sử dụng để thêm vào những giá trị khủng hơn, bé dại hơn hoặc bởi một quý giá nhất định.

Lưu ý: trong các công thức hàm SUMIF, một dấu đối chiếu theo sau bởi một trong những hoặc một văn phiên bản phải luôn luôn được đặt trong vết nháy kép (“”).

Điều kiệnToá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 những số to hơn 5 vào vùng A2:A10
Tính tổng nếu nhỏ hơn=SUMIF(A2:A10, “”&D1, B2:B10)Tính tổng những ô trong cột B, nếu những ô tương ứng, cùng loại trong cột A có mức giá trị khác quý hiếm ở D1
Tính tổng nếu to hơn hoặc bằng>==SUMIF(A2:A10, “>=5”)Tính tổng những 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

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 những công thức hàm SUMIF không giống nhau để đối chiếu đúng mự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 ví như bằngĐối chiếu trả toàn:=SUMIF(A2:A8, “bananas”, C2:C8)Giá trị tính tổng trong những ô C2:C8 trường hợp ô khớp ứng trong cột A chứa đúng chuẩn từ “bananas” với không dư một từ tốt kí tự nào. Những ô chứa “green bananas”, “bananas green” or “bananas!” không bao gồm trong trường vừa lòng này.
Đối chiếu từng phần:=SUMIF(A2:A8, “*bananas*”, C2:C8)Giá trị tính tổng trong số ô C2:C8 nếu như ô khớp ứng trong cột A chứa từ “bananas”, đứng hòa bình hoặc đi kèm theo với đầy đủ từ khác”. Các ô đựng “green bananas”, “bananas green” or “bananas!” sẽ được tính vào trường thích hợp này.
Tính tổng nếu như 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 trường hợp ô tương xứng trong cột A chứa bất kể giá trị nào mà khác “bananas”. Ví như ô đựng “bananas” có thêm 1 số từ hay kí từ khác, ví như “yellow bananas” hoặc “bananas yellow”, thì chúng sẽ tiến hành tính tổng.

Xem thêm: Cách chọn cpu phù hợp với main nào, hướng dẫn cách chọn cpu phù hợp với main

Đối chiếu từng phần:=SUMIF(A2:A8, “*bananas*”, C2:C8)Giá trị tính tổng trong các ô C2:C8 trường hợp ô tương ứng trong cột A chứa bất kể giá trị nào nhưng mà khác “bananas”. Ví như ô chứa “bananas” có thêm 1 số từ bỏ khác. Những ô như “yellow bananas” or “bananas yellow” sẽ không thỏa tiêu chí này.

Để biết thêm tin tức về giá trị khớp 1 phần, vui lòng xem ví dụ như về hàm SUMIF vào excel với những kí từ đại diện.

Và bây giờ, hãy xem công thức chính xác “Tính tổng giả dụ 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 kho của tất cả các thành phầm khác không tính “goldfinger bananas”:

= SUMIF (A2: A7, " goldfinger bananas", C2: C7) 

*

Lưu ý: tương đương như phần lớn các hàm Excel khác, SUMIF không phân biệt chữ hoa chữ thường, tức là dù các 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 rộng và hy vọng có một bí quyết hàm SUMIF nhiều hơn, chúng ta có thể thay thay giá trị số hoặc văn phiê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 thích hợp này, bạn sẽ không phải chuyển đổi công thức nhằm tính tổng có điều kiện dựa trên các tiêu chuẩn khác – mà bạn chỉ cần nhập một quý giá mới vào trong 1 ô được tham chiếu.

*

Chú thích. Khi bạn sử dụng một biểu thức xúc tích và ngắn gọn với một tham chiếu ô, các bạn phải thực hiện dấu nháy kép (“”) để ban đầu một chuỗi văn bạn dạng và dấu với (&) nhằm nối/kết thúc chuỗi, lấy ví dụ “” và F1.

Dấu “bằng” (=) rất có thể được bỏ lỡ trước một tham chiếu ô, vì thế cả hai công thức dưới đó là mang tính tương tự và chủ yếu 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 cùng với kí tự đại diện

Nếu các bạn mục đích của công ty là câu hỏi tính tổng bao gồm điều kiện những ô dựa trên tiêu chí “văn bản” và bạn muốn tính tổng bằng cách đối chiếu từng phần, thì bạn phải sử dụng những kí tự đại diện thay mặt trong bí quyết SUMIF của bạn.

Hãy coi xét các kí tự đại diện sau đây:

Dấu hoa thị (*) – đại diện cho những kí tự
Dấu hỏi (?) – đại diện cho một kí từ duy nhất tại một vị trí vậy thể

Ví dụ 1. Những giá trị tính tổng dựa trên so sánh từng phần:

Giả sử, bạn muốn tính toàn bô tiền tương quan đến tất cả các loại chuối (bananas). Thì những công thức SUMIF sau vẫn chạy như sau:

= SUMIF (A2: A8, "bananas *", C2: C8)– tiêu chí bao hàm các văn phiên bản kết thúc với lốt hoa thị (*).

= SUMIF (A2: A8, "*" & F1 & "*", C2: C8)– những tiêu chí gồm một tham chiếu ô dĩ nhiên dấu hoa thị, vui lòng để ý về việc sử dụng dấu với (&) trước cùng sau một tham chiếu ô nhằm nối cùng với chuỗi.

*

Nếu bạn chỉ hy vọng tính tổng phần nhiều ô bắt đầu hoặc dứt với một văn bản nhất định, thì bạn chỉ cần thêm một vệt * trước hoặc sau văn bản:

= SUMIF (A2: A8, "bananas*", C2: C8) – tổng vốn trong C2: C8 giả dụ một ô tương ứng trong cột A ban đầu bằng trường đoản cú “bananas”.

= SUMIF (A2: A8, "*bananas", C2: C8) – tổng mức vốn trong C2: C8 nếu như một ô tương ứng trong cột A xong bằng từ bỏ “bananas”.

Ví dụ 2. Tổng mức với số kí tự tuyệt nhất định

Trong ngôi trường hợp bạn muốn tính tổng một trong những giá trị nếu cực hiếm này có đúng chuẩn 6 chữ cái, thì bạn sẽ sử dụng công thức sau:

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

Ví dụ 3. Tính tổng những ô có giá trị tương ứng với cực hiếm văn bản

Nếu bảng tính của công ty chứa những loại dữ liệu khác biệt và bạn chỉ mong mỏi tổng các ô tương ứng với cực hiếm văn bản, thì các công thức SUMIF sau sẽ có ích cho bạn:

= SUMIF (A2: A8, "? *", C2: C8) – cùng giá trị trong những ô C2: C8 giả dụ ô 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ư là rỗng, chứa những chuỗi tất cả độ dài bởi 0 (là công dụng của những công thức khác), ví dụ: = “”.

Cả hai công thức trên đều vứt qua các giá trị không phải là văn phiên bản như các lỗi, các phép toán luận, các chữ số với ngày tháng.

Ví dụ 4. Thực hiện * hoặc ? giống như các kí trường đoản cú bình thường

Nếu bạn có nhu cầu * hoặc ? được nhìn nhận như là 1 trong những chữ chứ không phải là một trong những kí trường đoản cú đại diện, thì nên sử dụng dấu bửa trước kia (~). Ví dụ: bí quyết SUMIF sau vẫn thêm các giá trị trong các ô C2: C8 trường hợp một ô vào cột A phía trong cùng một hàng mà cất một vết chấm hỏi:

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

*

Tổng những số lớn số 1 hoặc nhỏ dại nhất vào một dải:

Để tính tổng những số lớn nhất hoặc nhỏ nhất vào dải, hãy áp dụng hàm SUM cùng rất hàm LARGE hoặc SMALL.

Ví dụ 1. Thêm một vài số lớn số 1 / nhỏ tuổi nhất

Nếu bạn có nhu cầu tính tổng chỉ một số trong những ít, ví dụ như 5 số, thì bạn có thể gõ chúng trực tiếp vào công thức, như sau:

= SUM (LARGE (B1: B10, 1,2,3,4,5)) – tổng 5 số mập nhất

= SUM (SMALL (B1: B10, 1,2,3,4,5)) – tổng 5 số nhỏ nhất

*

Chú thích. Nếu tất cả 2 hoặc nhiều hơn số được buộc mang lại vị trí cuối cùng, chỉ bao gồm số đầu tiên xuất hiện sẽ được tính. Như bạn thấy, số 9 vật dụng hai ko được sản xuất trong lấy ví dụ trên.

Ví dụ 2. Thêm vào những giá trị lớn/nhỏ

Nếu bạn muốn thêm những số, thay bởi liệt kê tất cả trong công thức, chúng ta cũng có thể tổ chức các hàm ROW cùng hàm INDIRECT trong phương pháp SUM của bạn. Trong hàm INDIRECT, hãy thực hiện 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 tiếp sau đây tính tổng 15 số lớn nhất và nhỏ dại nhất, thứu tự là:

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

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

Vì đấy là các bí quyết 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 đổi thay số trong những giá trị lớn nhất / nhỏ nhất

Nếu các bạn không muốn đổi khác công thức từng khi bạn có nhu cầu tính tổng một số ô không giống nhau, chúng ta cũng có thể đặt số đó trong một ô nào đó thay do nhập nó vào công thức. Với sau đó, bạn có thể sử dụng hàm ROW với INDIRECT như đã trình bày ở trên cùng tham chiếu một ô chứa biến, như vào trường hòa hợp này là ô E1:

= SUM (LARGE (B1: B50, ROW (INDIRECT ("1:" & E1)))) – tính tổng những giá trị hàng đầu mà có chứa 1 ô cố đổi

= SUM (SMALL (B1: B50, ROW (INDIRECT ("1:" và E1)))) – tính tổng những giá trị nhỏ nhất mà gồm chứa 1 ô chũm đổi

*

Hãy ghi nhớ rằng, đó là các phương pháp mảng, do vậy các bạn phải nhận Ctrl + Shift + Enter để dứt chúng.

Làm nạm nào để tính tổng những ô tương xứng với những ô trống

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

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

Nếu “trống” bao hàm các chuỗi độ dài bởi không (ví dụ: ô tất cả công thức như = “”), thì chúng ta cũng có thể sử dụng “” làm tiêu chí:

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

Cả hai bí quyết trên đều đánh giá các ô vào cột A với nếu có bất kỳ ô trống làm sao được kiếm tìm thấy, những giá trị tương xứng từ cột C sẽ tiến hành thêm vào.

*

Làm cầm cố nào nhằm tính tổng những ô khớp ứng với các ô không trống

Để sản xuất loại bí quyết “nếu ô không trống thì tính tổng”, hãy áp dụng “” có tác dụng tiêu chí. Làm việc này đã cộng tất cả các ô chứa bất kỳ thứ gì trong đó, bao hàm cả các chuỗi gồm độ dài bởi không.

Ví dụ: đó là cách chúng ta có thể tính tổng doanh số bán sản phẩm cho tất cả các khu vực vực, có nghĩa là trong đó cột B không trống:

=SUMIF(B2:B10, “”, C2:D10)

*
Ví dụ ví dụ về giải pháp dùng hàm sumif 4

Cách thực hiện Hàm SUMIF cùng 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 gồm điều kiện các giá trị dựa vào ngày cũng tương tự cách bạn áp dụng văn bạn dạng và các tiêu chuẩn 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, bé dại hơn hoặc bởi so với ngày chúng ta chỉ định, thì hãy sử dụng dấu đối chiếu mà cửa hàng chúng tôi đã đàm luận từ những bài xích trước. Và dưới đây là một vài ví dụ phương pháp hàm SUMIF:

Trong trường hợp bạn có nhu cầu tính tổng các giá trị dựa vào ngày hiện nay thời, thì các bạn phải sử dụng hàm SUMIF kết phù hợp với hàm TODAY (), như sau:

Tiêu chíCông thức ví dụ
Tính tổng những giá trị trong những ô dựa trên ngày hiện tại thời=SUMIF(B2:B9, TODAY(), C2:C9)
Tính tổng các giá trị ô mà nằm trong quá khứ, tức là trước ngày hiện nay thời.=SUMIF(B2:B9, “”&TODAY(), C2:C9)
Tính tổng những giá trị ô nếu những ngày tương xứng đều được xảy ra trong một tuần (có tức là ngày bây chừ + 7 ngày nữa)=SUMIF(B2:B9, “=”&TODAY()+7, C2:C9)

Hình tiếp sau đây minh hoạ cách bạn cũng có thể sử dụng công thức ở đầu cuối để search tổng số chi phí của toàn bộ các sản phẩm được chuyển đi trong một tuần.

*

Cách tính tổng những giá trị ứng với trong một quãng thời hạn nhất định

Câu hỏi sau đây được hỏi tiếp tục trên các diễn đàn và vabishonglam.edu.vn của Excel – “Làm biện pháp 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à thực hiện một sự kết hợp, hoặc chính xác hơn, là sự biệt lập giữa 2 bí quyết SUMIF. Vào Excel 2007 trở về bây giờ, chúng ta có thể sử dụng hàm SUMIFS cho phép nhiều tiêu chí, thậm chí là 1 trong lựa chọn tốt hơn. Chúng tôi sẽ nói tới vế sau trong chủ thể của nội dung bài viết tiếp theo, còn tiếp sau đây sẽ là ví dụ về bí quyết SUMIF:

*

Công thức này có thể có vẻ hơi khó khăn khăn nếu bạn chỉ lướt qua, tuy vậy khi ví như đi sâu vào nó, thì bạn sẽ thấy nó rất 1-1 giản. Hàm SUMIF trước tiên đã thêm toàn bộ các ô trong C2: C9, giả dụ ô khớp ứng trong cột B lớn hơn hoặc bằng ngày bước đầu (trong lấy một ví dụ này là ngày một tháng 10). Tiếp đó, bạn chỉ cần phải một số loại đi bất kỳ giá trị nào xẩy ra vào sau ngày ngừng (Oct-10 tuyệt ngày 31/10) – là mọi giá trị được trả về vì chưng hàm SUMIF đồ vật hai.

Làm thay nào để tính tổng các giá trị vào một vài cột

Để hiểu vụ việc hơn, hãy chăm chú ví dụ sau đây: giả sử bạn có một bảng nắm tắt lợi nhuận hàng tháng cùng nó được thu thập từ một số report cho từng khu vực vực, trong đó có một vài hồ nước sơ mang lại cùng một sản phẩm ở trên:

*

Vì vậy, làm cầm nào để chúng ta tìm thấy tổng thể apples (táo) được bán ở tất cả các tè bang trong ba tháng qua?

Như chúng ta đã biết, kích thước của sum_range được xác minh bởi kích cỡ của thông số range. Đó là tại sao tại sao chúng ta không thể sử dụng công thức như = SUMIF (A2: A9, “apples”, C2: E9) cũng chính vì nó vẫn thêm các giá trị khớp ứng với “Apples” cơ mà chỉ ngơi nghỉ trong cột C. Và đây không phải là hầu hết gì họ đang tìm kiếm kiếm.

Giải pháp hợp lí và dễ dàng nhất cho thấy chính nó là tạo thành một cột để trợ góp tính từng tổng số cho từng hàng và sau đó cột này sẽ được tham chiếu trong tiêu chuẩn sum_range.

Đi trước cùng đặt phương pháp SUM đơn giản trong ô F2, sau đó điền vào cột F: = SUM (C2: E2)

Sau đó, bạn có thể viết một phương pháp SUMIF đơn giản dễ dàng như sau:

= SUMIF (A2: A9, "apples", F2: F9)

hoặc là

= SUMIF (A2: A9, H1, F2: F9)

Trong các công thức trên, sum_range gồm cùng kích cỡ với dải, tức là 1 cột và 8 hàng, và bởi đó hiệu quả mà nó trả lại sẽ chính xác:

*

Nếu bạn muốn làm mà không có một cột trợ giúp, sau đó bạn có thể viết một cách làm SUMIF riêng cho từng cột bạn có nhu cầu tính tổng, và sau đó thêm các công dụng 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 giải pháp khác là áp dụng một bí quyết mảng phức tạp hơn (nhưng cũng hãy nhờ rằng nhấn Ctrl + Shift + Enter):

= SUM ((C2: C9 + D2: D9 + E2: E9) * (- (A2: A9 = I1)))}

Cả hai bí quyết trên sẽ đưa ra công dụng là 2070 trong trường đúng theo trên.

Những câu hỏi thường gặp gỡ hoặc “Tại sao phương pháp SUMIF của tớ không hoạt động?”

Có thể bao gồm vài lý do khiến cho Hàm SUMIF không hoạt động trong tình huống của bạn. Đôi khi phương pháp của các bạn sẽ không trả lại số đông gì bạn hy vọng đợi chỉ vày kiểu tài liệu trong một ô hoặc trong một vài đối số không cân xứng với hàm SUMIF. Bởi đó, dưới đây là một danh sách những vấn đề cần kiểm tra:

1. Những tham số của range và sum_range đề xuất là những dải, chứ chưa hẳn mảng:

Các tham số đầu tiên (range) và thứ bố (sum_range) trong bí quyết SUMIF của doanh nghiệp phải vẫn là một tham chiếu dải, như A1: A10. Trường hợp bạn mặc kệ mà làm lơ điều này, lấy một ví dụ là trường hòa hợp tham chiếu một mảng như 1,2,3, thì Excel cùng với trả lại cùng với một thông báo 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 vắt 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ư như phần đông các hàm Excel khác, SUMIF có thể tham chiếu đến những trang tính với bảng tính khác, miễn là chúng vẫn đang mở.

Ví dụ: công thức dưới đây sẽ tính tổng những giá trị trong số ô F2: F9 trong Sheet 1 (trang tính 1) của Book 1 (bảng tính 1) nếu có một ô khớp ứng trong cột A và bao gồm trang tính đó cũng có chứa “apples”:

= SUMIF ( Sheet1! $A$2:$A$9, "apples", Sheet1! $F$2:$F$9)

Tuy nhiên, công thức này đang không hoạt động ngay lúc Book 1 bị đóng góp lại. Điều này xảy ra vì những dải được tham chiếu bởi những công thức SUMIF vào bảng tính đã đóng, vậy cầ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 chất nhận được trong range với đối số sum_range, phải sau đó, phương pháp SUMIF sẽ trả về một thông báo lỗi #VALUE!.

3. Hãy chắc chắn rằng range cùng sum_range tất cả cùng kích cỡ:

Như đã lưu ý ở phần đầu của lý giải này, trong số phiên bản hiện đại của Microsoft Excel, các tham số range cùng sum_range không độc nhất thiết phải bởi nhau. Từ bỏ phiên bạn dạng Excel 2000 quay trở lại trước, range cùng sum_range có size không đồng đều có thể gây ra vấn đề. Tuy nhiên, ngay lập tức cả trong các phiên phiên bản mới độc nhất của Excel 2010 cùng 2013, các công thức phức tạp SUMIF nhưng mà ở đó, giả dụ sum_range có ít hàng và/hoặc cột hơn range, thì sẽ không hợp lệ. Đó là nguyên nhân tại sao nó được xem là một minh họa tốt để bạn dùng để ý rằng phải luôn luôn luôn giữ đối số range với sum_range cùng size và hình dạng.

4. Làm cố nào để phương pháp SUMIF của bạn chuyển động nhanh hơn:

Nếu các bạn đã điền vào bảng tính với các công thức tinh vi SUMIF làm lừ đừ Excel, hãy coi thử nội dung bài viết này và tìm hiểu Làm nắm nào nhằm chúng hoạt động nhanh hơn. Nội dung bài viết đã được viết cách đó khá lâu, vị vậy đừng không thể tinh được bởi thời gian tính toán. Các ví dụ về Excel được trình diễn trong lý giải này chỉ liên quan đến một vài cách sử dụng cơ bạn dạng của hàm này. Trong bài viết tiếp theo, công ty chúng tôi sẽ chuyển ra các công thức tiên tiến nhằm mục tiêu khai thác sức mạnh thực của SUMIF cùng SUMIFS và có thể chấp nhận được bạn tính tổng theo nhiều tiêu chí.

– – – – –

Ngoài ra để vận dụng Excel vào quá trình một cách kết quả thì các bạn còn bắt buộc sử dụng tốt các hàm, các công vắt khác của Excel.

Một số hàm cơ bản thường gặp gỡ như:

SUMIF, SUMIFS để tính tổng theo 1 điểm kiện, các đ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ý tài liệu dạng chuỗi, dạng ngày tháng, dạng số…Các hàm dò tra cứu tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ hay được 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à thực hiện Name trong công thức
Lập report với Pivot Table…

Rất nhiều kiến thức và kỹ năng phải ko nào? toàn bộ những kỹ năng và kiến thức này chúng ta đều có thể học được vào khóa học EX101 – Excel từ bỏ cơ bạn dạng tới chuyên viên của học tập Excel Online. Đây là khóa học giúp cho bạn hệ thống kiến thức và kỹ năng một biện pháp đầy đủ, bỏ ra tiết. Rộng nữa không còn có số lượng giới hạn về thời hạn học tập nên bạn có thể thoải mái học bất kể lúc nào, dễ ợt tra cứu vãn lại kỹ năng và kiến thức khi cần. Hiện thời hệ thống đang sẵn có ưu đãi cực kỳ lớn cho mình khi đăng ký tham gia khóa học.