Hàm Sumifs trong Excel hết sức hữu ích khi bạn phải tính tổng nhiều điều kiện. Sau đây là chi tiết cách sử dụng hàm Sumifs trong mailinhschool.edu.vn Excel.
Bạn đang xem: Cách sử dụng hàm sumifs trong excel 2007
Hàm SUMIFS vào Excel là một trong những hàm Excel cơ bản, hàm giám sát và đo lường thường được sử dụng trong Excel. Để tính tổng vào Excel họ sẽ cần sử dụng đến hàm SUM, nếu như muốn thêm một điều kiện duy nhất định cho hàm tính tổng này sẽ dùng hàm SUMIF. Vào trường đúng theo bảng tài liệu yêu ước tính tổng kèm theo rất nhiều điều kiện, thì bọn họ phải áp dụng tới hàm SUMIFS. Nội dung bài viết dưới đây sẽ hướng dẫn độc giả cách áp dụng hàm SUMIFS trong Excel.
Mục lục bài xích viết
Hướng dẫn cần sử dụng hàm SUMIFS trong ExcelCú pháp hàm SUMIFS vào Excel
Cú pháp hàm SUMIFS vào Excel tất cả dạng =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…).
Trong đó:
Sum_range: là những ô đề xuất tính tổng trong bảng dữ liệu, đầy đủ giá trị trống và cực hiếm text được bỏ qua, tham số cần phải có.Criteria_range1: phạm vi cần phải kiểm tra bằng đk criteria1, là giá trị bắt buộc.Criteria1: điều kiện áp dụng mang lại criteria_range1, có thể là một số, biểu thức, tham chiếu ô để khẳng định ô như thế nào trong criteria_range1 sẽ tiến hành tính tổng, cũng là giá trị bắt buộc.Criteria_range2,criteria2,…: tùy chọn những phạm vi và điều kiện bổ sung, buổi tối đa 127 cặp criteria_range, criteria.Lưu ý khi sử dụng hàm SUMIFS vào Excel
Mỗi ô vào vùng, phạm vi sum_range sẽ được cộng tổng lúc thỏa mãn tất cả các đk tương ứng đã khẳng định là hợp lý cho ô đó.Các ô vào sum_range gồm chứa TRUE được xem như là 1, những ô chứa FALSE được xem là 0.Mỗi criteria_range phải gồm cùng kích thước vùng chọn giống cùng với sum_range, criteria_range cùng sum_range phải bao gồm cùng số mặt hàng và thuộc số cột.Criteria hoàn toàn có thể dùng cam kết tự vệt hỏi chấm (?) núm cho ký tự đơn, vệt sao (*) thế cho một chuỗi. Nếu điều kiện là vệt hỏi chấm hay vết sao thì buộc phải nhập thêm lốt ~ sinh sống phía trước, giá bán trị đk là văn bản để trong vệt “.Nhiều điều kiện được vận dụng bằng xúc tích AND, tức là criteria1 VÀ criteria2, v.v...Mỗi phạm vi bổ sung cập nhật phải gồm cùng số lượng hàng với cột như tổng dải ô, nhưng các phạm vi không cần thiết phải liền kề. Nếu như bạn hỗ trợ các phạm vi không khớp, bạn sẽ gặp lỗi #VALUE.Chuỗi văn bạn dạng trong tiêu chuẩn phải được để trong lốt ngoặc kép (""), có nghĩa là "apple", ">32", "jap*"Tham chiếu ô trong tiêu chuẩn không được để trong dấu ngoặc kép, có nghĩa là "SUMIF với SUMIFS có thể xử lý những phạm vi, nhưng cấp thiết xử lý các mảng. Điều này tức là bạn tất yêu sử dụng những hàm khác như YEAR bên trên phạm vi tiêu chí vì công dụng là một mảng. Nếu khách hàng cần tác dụng này, hãy thực hiện hàm SUMPRODUCT.Thứ tự của những đối số giữa những hàm SUMIFS cùng SUMIF là khác nhau. Phạm vi tổng là đối số đầu tiên trong SUMIFS, dẫu vậy là đối số thứ tía trong SUMIF.Hướng dẫn dùng hàm SUMIFS trong Excel
Sử dụng các ký từ bỏ đại diện
Các ký kết tự đại diện thay mặt như ‘*’ và ‘?’ hoàn toàn có thể được sử dụng trong đối số tiêu chuẩn khi áp dụng hàm SUMIFS. Sử dụng các ký tự đại diện này sẽ giúp bạn kiếm tìm kiếm những hiệu quả trùng khớp tương tự nhưng không thiết yếu xác.
Dấu hoa thị (*) - Nó khớp với ngẫu nhiên chuỗi cam kết tự nào, hoàn toàn có thể được thực hiện sau, trước hoặc cùng với những tiêu chí xung quanh để chất nhận được sử dụng một trong những phần tiêu chí tìm kiếm kiếm.
Ví dụ, nếu khách hàng áp dụng các tiêu chuẩn sau trong hàm SUMIFS:
N * - Ngụ ý toàn bộ các ô vào phạm vi bước đầu bằng N* N - Ngụ ý toàn bộ các ô trong phạm vi xong bằng N* N * - những ô chứa NDấu chấm hỏi (?) - cân xứng với ngẫu nhiên ký tự đối chọi nào. đưa sử bạn vận dụng N?r làm cho tiêu chí. "?" ở chỗ này sẽ sửa chữa thay thế cho một ký kết tự duy nhất. N?r sẽ tương xứng với North, Nor, v.v... Mặc dù nhiên, nó sẽ không còn tính mang lại Name.
Điều gì sẽ xẩy ra nếu tài liệu đã cho chứa dấu hoa thị hoặc giấu chấm hỏi thực sự?
Trong trường hòa hợp này, chúng ta có thể sử dụng dấu vấp ngã (~). Bạn phải gõ “~” trước lốt chấm hỏi vào trường hòa hợp đó.
Sử dụng các Named Range với hàm SUMIFS
Named Range là tên mô tả của một tập hợp những ô hoặc dải ô vào một trang tính. Chúng ta có thể sử dụng những Named Range trong những lúc sử dụng hàm SUMIFS.
Ví dụ về hàm SUMIFS vào Excel
Chúng ta công thêm tổng cùng với bảng dữ liệu tiếp sau đây kèm theo một số điều kiện không giống nhau.
Ví dụ 1: Tính tổng sản phẩm mà nhân viên cấp dưới Hoài đẩy ra có đơn giá sản phẩm dưới 400.000đ.
Tại ô nhập kết quả bọn họ điền cách làm =SUMIFS(D2:D7,C2:C7,"Hoài",E2:E7,"D2:D7 là vùng bắt buộc tính tổng khía cạnh hàng.C2:C7 là vùng giá bán trị đk tên nhân viên.“Hoài” là điều kiện tên nhân viên cần tính số món đồ chứa trong vùng C2:C7.E2:E7 là điều kiện mặt hàng.“400.000” là đk chứa sản phẩm trong vùng E2:E7.
Kết quả ra tổng thể lượng sản phẩm như hình dưới đây.
Ví dụ 2: Tính tổng con số mặt hàng bán ra của nhân viên Nga tất cả số thứ tự khăn quàng cổ”). Trong số đó dấu dùng để vứt bỏ một đối tượng người tiêu dùng nào đó trong vùng dữ liệu làm điều kiện.
Kết quả tạo ra tổng số thành phầm như hình bên dưới đây.
Ví dụ 4:
Tính tổng toàn bộ chỉ số mang lại Pokemon thay hệ 1 hệ Thủy:
Điều khiếu nại ở đó là Water với Generation là 1.
Lưu ý: cỗ dữ liệu không thiếu tiếp tục sau mặt hàng 14, tất cả xuống bên dưới hàng 759.
Dưới trên đây là chi tiết từng bước:
Chọn ô H3.Gõ =SUMIFS.Click lưu ban vào lệnh SUMIFS.Chọn phạm vi tính tổng thể là C2:C759.Gõ ,Chọn phạm vi đến điều kiện thứ nhất B2:B759 (giá trị nhiều loại 1)Chọn tiêu chí (ô F3, có giá trị Water)Gõ ,Chọn phạm vi cho đk thứ nhì từ D2:D759 (giá trị Generation)Gõ ,Xác định tiêu chuẩn (ô G3 có giá trị 1).Nhấn Enter.Lưu ý: Bạn rất có thể thêm nhiều đk hơn bằng cách lặp lại quá trình từ 9 tới 12 trước lúc nhấn Enter.
Hàm này giờ đồng hồ tính tổng số của các chỉ số tổng mang lại Pokemon nước nuốm hệ vật dụng nhất. Hàm này hoàn toàn có thể được lặp lại cho các thế hệ sau để đối chiếu chúng.
Trên đấy là một số ví dụ về cách sử dụng hàm SUMIFS, tính tổng giá bán trị có khá nhiều điều kiện kết hợp. Họ phải chuẩn bị xếp các vùng và điều kiện đi kèm theo hợp lý thì Excel bắt đầu nhận dạng được công thức hàm nhằm tính.
Hạn chế của hàm SUMIFS
Hàm SUMIFS có một số trong những hạn chế mà chúng ta nên biết:
Các điều kiện trong SUMIFS được phối hợp bởi lô ghích AND. Nói phương pháp khác, tất cả các điều kiện phải là TRUE nhằm một ô được xem vào tổng. Để tính tổng các ô bằng lô ghích OR, bạn cũng có thể sử dụng một chiến thuật thay thế trong số trường hợp 1-1 giản.Hàm SUMIFS yêu mong phạm vi thực tiễn cho tất cả các đối số phạm vi; bạn không thể thực hiện một mảng. Điều này tức là bạn ko thể tiến hành những câu hỏi như trích xuất năm tự ngày bên trong hàm SUMIFS. Để chuyển đổi các giá trị mở ra trong đối số phạm vi trước khi áp dụng tiêu chí, hàm SUMPRODUCT là một giải pháp linh hoạt.SUMIFS không rành mạch chữ hoa chữ thường. Để tính tổng các giá trị dựa trên đk phân biệt chữ hoa chữ thường, chúng ta có thể sử dụng công thức dựa trên hàm SUMPRODUCT cùng với hàm EXACT.Cách thịnh hành nhất để xử lý các số lượng giới hạn trên là thực hiện hàm SUMPRODUCT. Trong phiên bản Excel hiện tại tại, một tùy chọn khác là sử dụng những hàm BYROW với BYCOL mới hơn.
Cách sửa lỗi #VALUE trong hàm SUMIFS của Excel
Vấn đề: Công thức tham chiếu tới ô trong workbook vẫn đóng
Hàm SUMIF tham chiếu tới một ô hoặc phạm vi ô trong workbook sẽ đóng vẫn cho tác dụng ở dạng báo lỗi #VALUE!.
Lưu ý: Đây là một lỗi phổ biến. Chúng ta cũng hay phát hiện thông báo này khi sử dụng các hàm Excel khác ví như COUNTIF, COUNTIFS, COUNTBLANK…
Giải pháp:
Mở workbook bên trong công thức này, thừa nhận F9 để làm mới lại công thức. Bạn có thể khắc phục vấn đề bằng phương pháp dùng cả nhị hàm SUM và IF trong cách làm mảng.
Xem thêm: Mặt Trời Mọc Hướng Nào Lặn Hướng Nào Và Lặn Hướng Nào? Mặt Trời Lặn Hướng Nào
Vấn đề: Chuỗi tiêu chí dài thêm hơn 255 kí tự
Hàm SUMIFS trả về tác dụng sai khi bạn cố gắng kết hợp các chuỗi dài hơn nữa 255 cam kết tự.
Giải pháp:
Rút ngắn chuỗi nếu có thể. Nếu như không thể thao tác làm việc đó, cần sử dụng hàm CONCATENATE hoặc toán tử & để phân chia giá trị thành các chuỗi. Ví dụ:
=SUMIF(B2:B12,"long string"&"another long string")
Vấn đề: trong SUMIFS, đối số criteria_range không nhất quán với đối số sum_range.
Đối số range luôn phải như là nhau vào SUMIFS. Điều đó có nghĩa các đối số criteria_range và sum_range cần tham chiếu tới cùng số hàng với cột.
Ở lấy một ví dụ sau, công thức trả về tổng lệch giá bán táo hàng ngày ở Bellevue. Mặc dù nhiên, đối số sum_range (C2:C10) không tương ứng với số hàng và cột sống criteria_range (A2:A12 và B2:B12). Dùng bí quyết =SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14) sẽ cho lỗi #VALUE.
Excel cho mailinhschool.edu.vn 365 Excel cho mailinhschool.edu.vn 365 dành đến m&#x
E1;y Mac Excel cho web Excel 2021 Excel 2021 mang đến Mac Excel 2019 Excel 2019 mang đến Mac Excel năm nhâm thìn Excel năm nhâm thìn cho Mac Excel 2013 Excel Web tiện ích Excel 2010 Excel Starter 2010 xem th&#x
EA;m...Ẩn bớt
Hàm SUMIFS, một trong những hàm toán học và lượng giác, cộng tất cả các đối số của chính nó mà đáp ứng nhiều tiêu chí. Ví dụ: bạn sẽ sử dụng SUMIFS để tính tổng số lượng các nhà nhỏ lẻ trong nước (1) ngụ tại một mã zip cùng (2) hữu dụng nhuận vượt vượt một giá trị cụ thể tính bởi đô la.
Cú pháp
SUMIFS(sum_range, criteria_range1, criteria1,
=SUMIFS(A2:A9,B2:B9,"=A*",C2:C9,"Tom")
=SUMIFS(A2:A9,B2:B9,"Chuối",C2:C9,"Tom")
| Sum_range (bắt buộc) | Phạm vi ô phải tính tổng. |
| Criteria_range1 (bắt buộc) | Phạm vi được kiểm tra bằng cách sử dụng Criteria1. Criteria_range1 và Criteria1 tùy chỉnh một cặp tìm kiếm kiếm, từ đó một phạm vi được kiếm tìm kiếm theo các tiêu chí cụ thể. Sau thời điểm tìm thấy những mục vào phạm vi, giá bán trị tương ứng của chúng trong Sum_range sẽ được cộng vào. |
| Criteria1 (bắt buộc) | Tiêu chí khẳng định ô làm sao trong Criteria_range1 sẽ tiến hành cộng vào. Ví dụ: tiêu chí có thể được nhập là 32, ">32", B4, "táo" hoặc "32". |
| Criteria_range2, criteria2, … (tùy chọn) | Các phạm vi bổ sung và các tiêu chí links với chúng. Chúng ta cũng có thể nhập về tối đa 127 cặp phạm vi/tiêu chí. |
Ví dụ
Để dùng phần lớn ví dụ này trong Excel, hãy kéo để chọn dữ liệu trong bảng, sau đó click chuột phải vào phần lựa chọn và lựa chọn Sao chép. Trong trang tính mới, nhấn vào phải vào ô A1 và lựa chọn Khớp Định dạng Đích dưới Tùy lựa chọn Dán.
| 5 | Táo | Tom |
| 4 | Táo | Sarah |
| 15 | Atisô | Tom |
| 3 | Atisô | Sarah |
| 22 | Chuối | Tom |
| 12 | Chuối | Sarah |
| 10 | Cà rốt | Tom |
| 33 | Cà rốt | Sarah |
| Công thức | Mô tả | |
| =SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, "Tom") | Cộng số lượng sản phẩm bắt đầu bằng A và người chào bán là Tom. Hàm áp dụng ký tự thay mặt * vào Criteria1, "=A*" nhằm tìm tìm tên thành phầm khớp trong Criteria_range1 B2:B9 và tìm kiếm tên "Tom" vào Criteria_range2 C2:C9. Tiếp nối hàm cộng các số trong Sum_range A2:A9 thỏa mãn nhu cầu cả nhị điều kiện. Tác dụng là 20. | |
| =SUMIFS(A2:A9, B2:B9, "Chuối", C2:C9, "Tom") | Cộng số lượng hàng hóa không cần là chuối cùng người phân phối là Tom. Hàm loại trừ chuối bằng trong criteria1, "Chuối", với tìm kiếm tên "Tom" vào Criteria_range2 C2:C9. Tiếp đến hàm cộng những số vào Sum_range A2:A9 thỏa mãn nhu cầu cả hai điều kiện. Kết quả là 30. |
Các vụ việc Chung
| 0 (Không) được hiển thị cụ vì kết quả mong đợi. | Đảm bảo Criteria1,2 phía bên trong dấu ngoặc kép nếu bạn đang khám nghiệm giá trị văn bản, chẳng hạn như tên người. |
| Kết trái sai khi Sum_range có mức giá trị TRUE hoặc FALSE. | Giá trị TRUE cùng FALSE đến Sum_range được định trị theo phong cách khác, điều này rất có thể dẫn đến tác dụng không mong ước khi cộng chúng. Các ô vào Sum_range có chứa TRUE định trị là 1. Các ô có chứa FALSE định trị là 0 (không). |
Những biện pháp thực hành tốt nhất
| Dùng ký kết tự đại diện. | Sử dụng những ký tự đại diện thay mặt như lốt chấm hỏi (?) và dấu sao (*) vào criteria1,2 hoàn toàn có thể giúp bạn tìm thấy tác dụng khớp tương tự nhưng không chính xác. Một dấu chấm hỏi khớp với một ký tự solo lẻ bất kỳ. Một lốt sao khớp cùng với chuỗi ký tự bất kỳ. Nếu bạn có nhu cầu tìm dấu chấm hỏi hay lốt sao thực sự, hãy nhập một lốt sóng (~) trước vệt chấm hỏi. Ví dụ: =SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, "To?") đã thêm tất cả các trường bao gồm tên ban đầu bằng "To" và chấm dứt bằng một chữ cái cuối cùng rất có thể thay đổi. |
| Hiểu rõ sự biệt lập giữa SUMIF và SUMIFS. | Thứ tự các đối số của hàm SUMIFS và SUMIF khác nhau. Cụ thể, đối số sum_range là đối số thứ nhất vào hàm SUMIFS, tuy nhiên nó là đối số thứ bố trong hàm SUMIF. Đây là vì sao phổ biến tạo ra vấn đề lúc sử dụng những hàm này. Nếu bạn sẽ sao chép và sửa những hàm tương tự nhau này, hãy bảo đảm bạn đặt các đối số theo đúng thứ tự. |
| Sử dụng cùng một trong những hàng với cột mang đến đối số phạm vi. | Đối số Criteria_range đề xuất chứa số hàng với số cột bởi với đối số Sum_range. |
Đầu Trang
Bạn yêu cầu thêm trợ giúp?
Bạn luôn có thể hỏi một chuyên viên trong xã hội Kỹ thuật Excel hoặc thừa nhận sự cung ứng trong
Cộng đồng trả lời.
Xem đoạn clip về cách dùng các hàm IF nâng cao như SUMIFS
Hàm SUMIF chỉ cộng số đông giá trị thỏa mãn nhu cầu một tiêu chuẩn đơn
Hàm SUMSQ cộng nhiều giá trị sau khi thực hiện tại phép toán bình phương trên mỗi cực hiếm đó
