Bạn đang xem bài viết Cách Sử Dụng Hàm Sumifs Và Sumif Với Nhiều Điều Kiện được cập nhật mới nhất trên website Utly.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
SUMIF (range, điều kiện, [sum_range])
range – dải của các ô được đánh giá theo điều kiện mà bạn đưa ra, mang tính bắt buộc.
criteria – điều kiện cần phải đáp ứng, mang tính bắt buộc
sum_range – các ô tính tổng nếu thỏa điều kiện, mang tính tùy chọn.
Như bạn thấy, cú pháp của hàm Excel SUMIF chỉ cho phép một điều kiện. Tuy nhiên, bên trên, chúng tôi có nói rằng Excel SUMIF có thể được sử dụng để tính tổng các giá trị với nhiều điều kiện. Làm thế nào mà có thể được? Thực tế, bạn cần thêm các kết quả của vài hàm SUMIF và sử dụng các công thức SUMIF với các điều kiện mảng, như trong ví dụ tiếp theo.
HÀM SUMIFS – CÚ PHÁP VÀ CÁCH SỬ DỤNG:
Bạn sử dụng SUMIFS trong Excel để tìm một tính tổng có giá trị các giá trị dựa trên nhiều điều kiện. Hàm SUMIFS đã được giới thiệu trong Excel 2007, vì vậy bạn có thể sử dụng nó trong tất cả các phiên bản của Excel 2013, 2010 và 2007.
So với SUMIF, thì cú pháp SUMIFS phức tạp hơn một chút:
sum_range – một hoặc nhiều ô tính tổng, mang tính bắt buộc. Đây có thể là một ô duy nhất, một dải ô hoặc dải có tên. Chỉ có các ô chỉ chứa số mới được tính tổng; Còn giá trị ô trống và giá trị văn bản thì sẽ bị bỏ qua.
Chú ý: Hàm SUMIFS hoạt động với biểu thức logic với AND, nghĩa là mỗi ô trong đối số sum_range chỉ được tính tổng nếu tất cả các điều kiện được chỉ định là đúng cho ô đó.
Khi bạn đang tiếp thu một cái mới mẻ, bạn nên bắt đầu với những điều đơn giản. Vì vậy, để bắt đầu, hãy xác định tất cả các đối số cho công thức SUMIFS của chúng tôi:
Bây giờ tập hợp các thông số trên, và bạn sẽ nhận được công thức SUMIFS sau:
= SUMIFS (C2: C9, A2: A9, “táo”, B2: B9, “Pete”)
Để khiến việc chỉnh sửa công thức đơn giản hơn, bạn có thể thay thế các tiêu chuẩn văn bản “apples” và “Pete” bằng các tham chiếu ô. Trong trường hợp này, bạn sẽ không phải thay đổi công thức để tính toán lượng trái cây khác từ một nhà cung cấp khác nhau:
= SUMIFS (C2: C9, A2: A9, F1, B2: B9, F2)
SỬ DỤNG SUMIFS VÀ SUMIF TRONG EXCEL – MỘT SỐ ĐIỀU CẦN NHỚ:
Mặc dù nét chung thì khá rõ ràng – tương đồng về điểm đến cho kết quả cuối cùng và các tham số – trong khi sự khác biệt dù không rõ rang nhưng vẫn rất cần thiết.
1. TRÌNH TỰ CỦA CÁC ĐỐI SỐ
Trong các hàm Excel SUMIF và SUMIFS, thứ tự các đối số là khác nhau. Cụ thể, sum_range là tham số thứ nhất trong SUMIFS, nhưng lại đứng thứ 3 trong công thức SUMIF.
Khi bắt đầu học, bạn sẽ cảm thấy có vẻ như Microsoft đã cố ý làm phức tạp hóa cho người học và người dùng nó. Tuy nhiên, khi xem xét kỹ hơn, bạn sẽ thấy lý do thực chất đằng sau nó. Vấn đề là sum_range là tùy chọn trong SUMIF. Nếu bạn bỏ qua nó, không có vấn đề, SUMIF công thức của bạn sẽ tính tổng các giá trị trong range (tham số đầu tiên).
Trong SUMIFS, sum_range là rất quan trọng và là bắt buộc, và đó là lý do tại sao nó đến trước. Có thể những người của Microsoft nghĩ rằng sau khi thêm các dải/điều kiện xem xét thứ 10 hoặc 100, thì có ai đó có thể quên xác định dải để tính tổng:)
Tóm lại, nếu bạn đang sao chép và chỉnh sửa các hàm này, hãy đảm bảo bạn đặt các thông số theo thứ tự đúng.
2. KÍCH THƯỚC CỦA ĐỐI SỐ SUM_RANGE VÀ CRITERIA_RANGE
Trong hàm SUMIF, đối số sum_range không nhất thiết phải có cùng kích thước với đối số range, miễn là bạn có ô phía trên bên trái. Trong hàm SUMIFS, mỗi criteria_range phải chứa cùng một số hàng và cột như tham số sum_range.
Ví dụ, công thức = SUMIF (A2: A9, F1, C2: C18) sẽ trả lại kết quả đúng vì Excel chỉ xem ô phía trên bên trái trong đối số sum_range (C2 trong ví dụ này là đúng) và sau đó bao gồm số cột và hàng giống như kích thước của đối số range.
Công thức SUMIFS: = SUMIFS (C2: C9, A2: A9, “apples”, B2: B10, “Pete”) sẽ báo lỗi # VALUE! vì criter_range2 (B2: B10) không khớp với criteria_range1 (A2: A9) và sum_range (C2: C9).
Có vẻ như chúng ta đã tiếp cận tạm xong lý thuyết rồi, nên mục sau chúng ta sẽ chuyển sang thao tác thực hành (chính là các ví dụ công thức:)
LÀM THẾ NÀO ĐỂ SỬ DỤNG SUMIFS TRONG EXCEL – VÍ DỤ CÔNG THỨC
VÍ DỤ 1. CÔNG THỨC SUMIFS VỚI TOÁN TỬ SO SÁNH
Lưu ý: Hãy lưu ý rằng trong các công thức SUMIFS, các biểu thức lôgic với các toán tử so sánh phải luôn luôn được đặt trong dấu nháy kép (“”).
Trong trường hợp bạn muốn tính tổng các giá trị với nhiều điều kiện dựa trên ngày hiện tại, hãy sử dụng hàm TODAY () trong điều kiện của hàm SUMIFS của bạn, như được trình bày bên dưới. Công thức sau đây tính tổng giá trị trong cột D nếu ngày tương ứng trong cột C rơi vào khoảng thời gian 7 ngày vừa qua, có bao gồm ngày hôm nay:
Chú thích. Khi bạn sử dụng một hàm Excel khác cùng với toán tử logic trong các điều kiện, bạn phải sử dụng ký hiệu và (&) để nối với 1 chuỗi, ví dụ “<=” & TODAY ().
Tương tự như vậy, bạn có thể sử dụng hàm SUMIF để tính tổng các giá trị trong một dải có dữ liệu là ngày xác định. Ví dụ: công thức SUMIFS sau sẽ thêm các giá trị trong các ô C2: C9 nếu ngày trong cột B rơi trong khoảng giữa ngày 1 tháng 10 năm 2014 và ngày 31 tháng 10 năm 2014, như sau:
Kết quả tương tự có thể đạt được bằng cách tính toán sự khác biệt của hai hàm SUMIF, như được minh họa trong ví dụ này – Cách sử dụng SUMIF để tính tổng các giá trị trong một dải ngày xác định. Tuy nhiên, bạn cũng thấy rằng công thức SUMIFS là dễ dàng hơn và dễ hiểu hơn nhiều, phải không nào?
Hướng Dẫn Cách Sử Dụng Hàm Sumif Để Tính Tổng Theo Một Điều Kiện Cho Trước
Cú pháp của hàm SUMIF trong Excel
Hàm có cú pháp như sau:
Trong đó:
Range: Là vùng bảng chứa điều kiện
Criteria: Là điều kiện làm căn cứ tính tổng. Điều kiện này sẽ có thể xuất hiện ở Range
Sum_range: Là vùng bảng chứa các giá trị cần tính tổng.
Range và Criteria là bắt buộc phải có trong hàm, còn Sum_range có thể không cần viết trong trường hợp bạn muốn tính tổng trực tiếp tại Range (tức là Sum_range chính là Range thì không cần viết lại).
Ví dụ về hàm SUMIF
Ví dụ: Tính tổng chi phí lương theo các bộ phận: Kinh doanh, Kế toán, Sản xuất
Để thực hiện yêu cầu này, chúng ta sẽ xét:
– Điều kiện cần tính là gì? Cụ thể là các bộ phận: Kinh doanh (tương ứng với ô F4), Kế toán (tương ứng với ô F5), Sản xuất (tương ứng với ô F6).
– Vùng nào chứa các điều kiện? chính là cột Bộ phận (vùng C3:C17)
– Kết quả cần tính ở đâu? chính là cột Tiền lương (vùng D3:D17)
Khi đó công thức SUMIF có thể viết như sau:
Tại ô F4 =SUMIF(C3:C17, F4, D3:D17)
Kết quả nhận được là:
ô F5 = SUMIF(C3:C17, F5, D3:D17)
ô F6 = SUMIF(C3:C17, F6, D3:D17)
Chú ý khi dùng hàm SUMIF
Để có thể áp dụng công thức SUMIF cho các bộ phận Kế toán, Sản xuất, các bạn có thể làm bằng cách sửa lại công thức:
Nhưng như vậy sẽ mất thời gian phải không nào. Hãy để ý các vùng C3:C17, D3:D17 không hề thay đổi trong công thức này. Do đó bạn có thể cố định tọa độ vùng C3:C17, D3:D17 bằng cách đặt dấu $ vào trước tọa độ các dòng, các cột (hoặc bôi đen từng vùng tọa độ này trong công thức rồi nhấn phím F4). Khi đó công thức tại ô F4 sẽ được viết lại là:
=SUMIF($C$3:$C$17, F4, $D$3:$D17)
Bây giờ bạn có thể kéo công thức từ ô F4 xuống các ô F5, F6 và thu được kết quả
Việc kéo công thức từ trên xuống dưới còn được gọi là kỹ thuật FillDown.
Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table… hay kỹ năng làm báo cáo tổng hợp, kỹ năng làm báo cáo chi tiết… Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EXG01 – Tuyệt đỉnh Excel – Trở thành bậc thầy Excel trong 16 giờ. Hãy nhanh tay đăng ký để nhận được nhiều ưu đãi hấp dẫn.
Tài liệu kèm theo bài viết
Hàm Vlookup Nhiều Điều Kiện, Cách Dùng Vlookup Nhiều Điều Kiện.
Hàm Vlookup nhiều điều kiện trong Excel là hàm tìm kiếm trá trị trong chuỗi với những yêu cầu cao cấp mà hàm vlookup thông thường không xử lý được. Nó giúp ta dò tìm dữ liệu theo cột với điều kiện dò tìm được kết hợp từ nhiều cột. Trong bài viết này, chúng tôi sẽ hướng dẫn bạn sử dụng hàm Vlookup nhiều điều kiện qua các ví dụ cụ thể, thực tế và dễ hiểu.
Hàm vlookup nhiều điều kiện thực chất là hàm Vlookup thông thường, nhưng để sử dụng được thì ta cần kết hợp một số hàm khác nhằm biến đổi điều kiện dò tìm sao cho hàm Vlookup hiểu và trả về kết quả đúng.
1. Chức năng của hàm Vlookup trong Excel.
Trong Excel chúng ta sử dụng hàm Vlookup để tìm kiếm dữ liệu trong bảng hoặc một phạm vi theo cột trong một bảng dò tìm đã định nghĩa trước. Như vậy, chức năng chính của hàm Vlookup là dùng để tìm kiếm giá trị trong một bảng giá trị cho trước.
2. Cú pháp hàm Vlookup trong excel.
Trong đó:
Lookup_value (bắt buộc): Giá trị cần tìm, có thể là ô tham chiếu, một giá trị hoặc chuỗi văn bản.
Table_array (bắt buộc): Bảng tìm kiếm giá trị gồm hai cột dữ liệu trở lên. Có thể là mảng thường, được đặt tên hoặc bảng Excel. Cột chứa giá trị tìm kiếm phải được đặt đầu tiên của Table_array.
Row_index_num (bắt buộc): Số thứ tự của cột chứa kết quả trả về trong Table_array.
Range_lookup (tuỳ chọn): Một giá trị logic (Boolean) cho biết hàm VLOOKUP cần phải tìm kết quả chính xác hay tương đối.
Nếu TRUE hoặc bỏ qua, kết quả khớp tương đối được trả về. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy, hàm Vlookup của bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value.
Nếu FALSE, chỉ kết quả khớp chính xác được trả về. Nếu không giá trị nào trong hàng chỉ định khớp chính xác với giá trị tìm kiếm, hàm Vlookup sẽ trả về lỗi #N/A
3. Hàm Vlookup nhiều điều kiện.
Từ công thức trên, ta có thể thấy được hàm Vlookup thuông thường chỉ tìm kiếm được với điều kiện dò tìm là 1 ô tham chiếu, một giá trị, hoặc một chuỗi văn bản. Vậy nếu điều kiện dò tìm nằm ở nhiều ô hoặc từ nhiều chuỗi văn bản trở lên thì chắc chắn ta không thể sử dụng hàm Vlookup thông thường được. Ở đây, chúng tôi sẽ giới thiệu cho bạn 2 cách để tìm kiếm với hàm vlookup nhiều điều kiện.
Cách 1: Dùng cột phụ.
Cách 2: Dùng công thức mảng.
Ưu nhược điểm của 2 cách dùng hàm Vlookup nhiều điều kiện trong Excel.
Sử dụng cột phụ:
Ưu điểm: Đơn giản, dễ dùng và dễ áp dụng.
Nhược điểm: Làm tăng lượng dữ liệu và tìm kiếm bởi việc tạo thêm cột phụ.
Sử dụng công thức mảng:
Ưu điểm: Không làm tăng lượng dữ liệu, không gây dư thừa.
Nhược điểm: Khó dùng, khó nhớ, công thức dài, trừu tượng.
4. Hàm Vlookup nhiều điều kiện sử dụng cột phụ.
4.1 Dùng hàm Vlookup nhiều điều kiện sử dụng cột phụ.
Cách đơn giản nhất để tìm kiếm trong Excel với nhiều điều kiện là sử dụng cột phụ. Từ nhiều điều kiện mình tổng hợp lại vào 1 cột mới và dùng hàm Vlookup để tìm kiếm với điều kiện dò tìm là cột ta vừa tạo.
Tùy vào từng yêu cầu cụ thể mà ta sẽ ghép các điều kiện bằng các cách khác nhau, nhưng thông thường Cột phụ sẽ được tạo ra bằng các ghép các điều kiện bằng kí hiệu “&”.
VD: A1 chứa giá trị là: NV. B1 chứa giá trị là 10.
Để dễ hình dung hơn về cách sử dụng hàm Vloolup với cột phụ chúng ta cùng tìm hiểu một vài ví dụ minh họa.
4.2 Ví dụ sử dụng hàm Vlookup nhiều điều kiện bằng cột phụ.
VD: Bạn có một danh sách sản lượng sản xuất cho từng sản phẩm, từng ca, từng nhân viên. Làm thế nào để biết được sản lượng của 1 sản phẩm nào đó trong từng ca theo từng nhân viên là bao nhiêu?
Hình 1: Hàm Vlookup nhiều điều kiện sử dụng cột phụ.
Với bài toán tìm kiếm dữ liệu theo hàng ngang trong Excel chắc chắn bạn sẽ nghĩ ngay đến hàm Vlookup, nhưng ở đây ta cần tìm sản lượng của từng sản phẩm trong từng ca theo từng nhân viên (3 điều kiện) mà hàm vlookup thông thường lại chỉ dùng được với 1 điều kiện.
Nên ta cần biến đổi điều kiện đầu vào từ 3 điều kiện thành 1 điều kiện bằng cách tạo ra một cột phụ mới từ việc ghép cột Sản phẩm, cột Ca và cột Nhân viên.
Các bước thực hiện:
B1: Tạo cột phụ.
B2: Viết hàm với điều kiện tìm kiếm là cột phụ vừa tạo.
Chi tiết các bước:
B1: Tạo cột phụ:
Ta tạo thêm cột mới, cột này đứng ở trước cột Sản phẩm và được tạo ra bằng cách ghép cột Sản phẩm, cột Ca và cột Nhân viên.
Công thức ghép: [Sản phẩm]&[Ca]&[Nhân viên]
Với hàng đầu tiền thì công thức sẽ là: B5&C5&D5
Sau khi tạo công thức cho hàng đầu ta copy công thức đó cho các hàng tiếp theo để hoàn thành cột.
Sau khi hoàn thành thì cột phụ sẽ có dạng như hình sau:
Hình 2: Hàm Vlookup sử dụng cột phụ.
Bước 2: Viết công thức tìm kiếm với cột phụ vừa tạo.
Trong đó:
Quan sát hình dưới để hiểu hơn về các thành phần của công thức chúng ta vừa tạo.
Hình 3: Hàm Vlookup sử dụng cột phụ.
Nếu muốn tìm sản lượng của sản phẩm khác hoặc ca khác với nhân viên khác bạn chỉ cần nhập sản phẩm, ca và nhân viên cần tìm vào bảng tìm kiếm như trên hình mà không cần tạo lại công thức.
4.3 Những lưu ý khi sử dụng hàm Vlookup nhiều điều kiện bằng cột phụ.
Về bản chất: Hàm Vlookup nhiều điều kiện khi dùng cột phụ sẽ trở thành hàm vlookup 1 điều kiện thông thường.
Cột phụ được tạo ra phải được đặt ở đầu bảng dò tìm.
Khi tạo cột phụ ta phải ghép các cột lại theo thứ tự giống với thứ tự của điều kiện dò tìm.
5. Hàm Vlookup nhiều điều kiện sử dụng công thức mảng.
Công thức mảng là một cách tính nâng cao trong Excel mà không phải ai học cũng chú ý tới nó. Chắc hẳn, trên 90% các bạn đọc bài viết này sẽ không biết tới việc sử dụng công thức mảng để tìm kiếm với nhiều điều kiện. Bởi hầu hết mọi người ít biết tới mảng trong Excel và cách này tính này khá phức tạp với những bạn chưa thành thạo Excel. Nên các bạn có thể tìm kiếm bằng phương pháp tạo cột phụ phía trên.
Mảng là một hàng giá trị hay một cột giá trị hoặc là một kết hợp các hàng và cột giá trị. (ví dụ: {1,2,3,4})
Công thức mảng là công thức được bao bởi cặp dấu ngoặc nhọn {} do excel tự động thêm vào sau khi kết thúc nhập công thức. Công thức mảng là công thức có thể thực hiện nhiều phép tính đối với một hoặc nhiều mục trong mảng. Công thức mảng có thể trả về nhiều kết quả hoặc một kết quả duy nhất.
Công thức mảng bao gồm nhiều ô được gọi là công thức đa ô và công thức mảng trong một ô duy nhất được gọi là công thức đơn ô.
Nhập công thức mảng:
Chọn ô hoặc vùng ô cần nhập công thức. Nhập công thức cần tính toán.
Nhấn tổ hợp phím Ctrl + Shift + Enter.
5.2 Cách dùng hàm Vlookup nhiều điều kiện sử dụng công thức mảng.
Khi sử dụng công thức mảng chúng ta sẽ không phải tạo thêm cột, không làm tăng lượng dữ liệu và tìm kiếm.
Để tìm kiếm ta kết hợp sử dụng hàm Vlookup với hàm Choose. Và công thức mảng được lồng trong hàm Choose.
5.3 Ví dụ về hàm Vlookup nhiều điều kiện sử dụng công thức mảng.
Như ví dụ bên trên ta tiếp tục tìm kiếm sản lượng của một sản phẩm theo từng ca.
Để tìm kiếm sản lượng của 1 sản phẩm theo ca tại ô I8 ta nhập công thức: =VLOOKUP(I5&I6&I7,CHOOSE({1,2},C5:C9&D5:D9&E5:E9,F5:F9), 2,) và nhấn Ctrl + Shift + Enter.
Trong đó:
I5&I6&I7: Là kết hợp 3 điều kiện cần tìm.
CHOOSE({1,2},C5:C9&D5:D9&E5:E9,F5:F9): Là bảng dò tìm, bảng này được tạo từ 1 mảng 2 chiều có 2 cột.
Cột 1: Cột tham chiều.
Cột 2: Cột giá trị trả về.
2: Số thứ tự cột dữ liệu trên bảng dò tìm.
: Kiểu tìm kiếm chính xác.
Hình 4: Tìm kiếm bằng công thức mảng.
Đọc đến đây chắc hẳn nhiều bạn vẫn chưa hiểu được về hàm Choose mà ta đã sử dụng.
Các bạn cùng quan sát hình bên dưới để hiểu hơn về hàm Choose.
Hình 5: Giá trị trả về của hàm Choose trong Excel.
Giải thích công thức: = CHOOSE({1,2},C5:C9&D5:D9&E5:E9,F5:F9 )
Trong đó:
{1,2} : Công thức này sẽ trả về 1 mảng 2 chiều gồm 2 cột. Cột 1 được lấy ở ngay sau dấu phẩy thứ nhất, cột 2 được lấy ở sau dấu phẩy thứ 2.
C5:C9&D5:D9&E5:E9 : Giá trị trả về là 1 cột, các giá trị trong cột này được ghép từ cột Ca, cột Sản phẩm và cột Nhân viên.
F5:F9 : Là cột sản lượng.
Chú ý: Hình 5 chỉ để các bạn hiểu hơn về cách hoạt động của hàm CHOOSE trong Excel, khi dùng với hàm Vlookup các bạn chỉ cần viết hàm CHOOSE lồng trong hàm Vlookup như trong hình 4.
5.4 Lưu ý khi sử dụng hàm Vlookup nhiều điều kiện bằng công thức mảng.
Khi nhập xong công thức bạn nhớ nhấn tổ hợp phím Ctrl + Shift + Enter.
Dấu ngoặc nhọn {} bao lại công thức mảng là do excel tự động thêm vào sau khi kết thúc nhập công thức.
Hàm Vlookup nhiều điều kiện dùng công thức mảng khá khó dùng nhưng nếu bạn thành thạo nó sẽ hỗ trợ bạn rất nhiều trong việc dò tìm và thống kê.
Nếu bạn không thực sự thành thạo thì nên bỏ qua cách này để tránh viết sai công thức dẫn đế tính toán sai.
Gợi ý học tập mở rộng.
Trọn bộ khoá học Excel cơ bản miễn phí: Học Excel cơ bản
Hàm Sumif: Công Thức Và Cách Sử Dụng
Hướng dẫn cho thấy cách thực hiện hàm SUMIF trong Excel để tính tổng các ô có điều kiện. Trọng tâm chính của chúng tôi sẽ là các ví dụ về công thức trong cuộc sống thực với tất cả các loại tiêu chí bao gồm ngày tháng, văn bản, số, ký tự đại diện, khoảng trống và không khoảng trống.
Microsoft Excel có một số chức năng để tóm tắt các tập dữ liệu lớn cho các báo cáo và phân tích. Một trong những hàm hữu ích nhất có thể giúp bạn hiểu được một tập hợp dữ liệu đa dạng khó hiểu là hàm SUMIF. Thay vì cộng tất cả các số trong một phạm vi, nó cho phép bạn chỉ tính tổng những giá trị đáp ứng tiêu chí của bạn.
Hàm SUMIF trong Excel
Hàm SUMIF trong Excel trả về tổng giá trị trong một phạm vi đáp ứng điều kiện bạn chỉ định.
Nó có cú pháp và đối số sau:
SUMIF (phạm vi, tiêu chí, [sum_range])
Ở đâu:
Phạm vi (bắt buộc) – phạm vi ô được đánh giá bởi tiêu chí.
Tiêu chí (bắt buộc) – điều kiện xác định ô nào sẽ được thêm vào. Nó có thể được cung cấp dưới dạng số, văn bản, ngày tháng, tham chiếu ô, biểu thức logic hoặc hàm.
Sum_range (tùy chọn) – phạm vi để cộng các số. Nếu bỏ qua, thì phạm vi là tổng hợp.
Hàm có sẵn trong tất cả các phiên bản Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 và các phiên bản cũ hơn.
Công thức hàm SUMIF cơ bản
Đối với người mới bắt đầu, hãy xây dựng công thức hàm SUMIF trong Excel ở dạng đơn giản nhất.
Trong bảng mẫu bên dưới, giả sử bạn muốn nhận được tổng doanh số cho một khu vực cụ thể như North. Để hoàn tất, chúng tôi xác định các đối số sau:
Phạm vi – danh sách các vùng (B2: B10).
Tiêu chuẩn – “North” hoặc ô chứa vùng quan tâm (F1).
Sum_range – số tiền bán hàng được cộng dồn (C2: C10).
Đặt các đối số lại với nhau, chúng ta nhận được các công thức sau:
=SUMIF(B2:B10, "north", C2:C10)
hoặc là
=SUMIF(B2:B10, F1, C2:C10)
Cả hai chỉ tính tổng các ô cho Bắc khu vực:
Cách sử dụng hàm SUMIF trong Excel
Thoạt nhìn đơn giản, SUMIF là một hàm khá phức tạp. Những lưu ý sau đây sẽ giúp bạn hiểu rõ hơn về logic bên trong của nó và tránh những lỗi thường gặp.
1. SUMIF chỉ hỗ trợ một tiêu chí
Cú pháp của hàm SUMIF chỉ có chỗ cho một điều kiện. Để tính tổng với nhiều tiêu chí, hãy sử dụng SUMIFS chức năng (thêm các ô đáp ứng tất cả các điều kiện) hoặc xây dựng một Công thức SUMIF với nhiều tiêu chí OR (tính tổng các ô đáp ứng bất kỳ điều kiện nào).
2. Cú pháp hàm SUMIF
Nếu tiêu chí đối số bao gồm một giá trị văn bản, ký tự đại diện hoặc toán tử lôgic theo sau là văn bản, số hoặc ngày, đặt toàn bộ tiêu chí trong dấu ngoặc kép. Ví dụ:
3. Range và sum_range phải có cùng kích thước
Để công thức hàm SUMIF hoạt động chính xác, sum_range đối số phải có cùng kích thước với phạm vi, nếu không bạn có thể nhận được kết quả sai lệch. Vấn đề là sum_range chỉ xác định ô phía trên bên trái của phạm vi sẽ được tính tổng, khu vực còn lại được xác định bởi kích thước và hình dạng của phạm vi tranh luận.
=SUMIF(B2:B10, "north", C2:D10)
4. Range và sum_range không được là mảng
Mặc dù SUMIF là hàm có thể xử lý một hằng số mảng trong tiêu chí như được hiển thị trong ví dụ này, nó không hỗ trợ các mảng trong phạm vi và sum_range. Hai đối số này chỉ có thể là phạm vi ô.
5. Hàm SUMIF không nhận dạng chữ hoa chữ thường
Theo thiết kế, SUMIF trong Excel là hàm không phân biệt chữ hoa chữ thường, nghĩa là nó xử lý các chữ cái viết hoa và viết thường là các ký tự giống nhau. Để làm một công thức SUMIF phân biệt chữ hoa chữ thường, sử dụng hàm SUMPRODUCT cùng với EXACT.
Ví dụ về công thức hàm SUMIF trong Excel
Bây giờ bạn đã biết các quy tắc chính của việc sử dụng hàm SUMIF trong Excel, đã đến lúc áp dụng kiến thức của bạn vào một số thứ thực tế và cải thiện các kỹ năng bạn đã học.
Ví dụ 1. SUMIF lớn hơn hoặc nhỏ hơn
Để tính tổng các số lớn hơn hoặc nhỏ hơn một giá trị cụ thể, hãy định so sánh dùng hàm SUMIF với một trong các điều sau toán tử logic:
=SUMIF(B2:B10, "<200")
Ví dụ 2. SUMIF bằng
Công thức SUMIF với tiêu chí “bằng” hoạt động cho cả số và văn bản. Trong các tiêu chí như vậy, dấu bằng không thực sự bắt buộc.
=SUMIF(C2:C10, 3, B2:B10)
hoặc là
=SUMIF(C2:C10, "=3", B2:B10)
Đến tổng nếu bằng ô, chỉ cần cung cấp tham chiếu ô cho các tiêu chí:
=SUMIF(C2:C10, F1, B2:B10)
Trong đó B2: B10 là số lượng, C2: C10 là thời gian vận chuyển và F1 là thời gian giao hàng mong muốn.
=SUMIF(A2:A10, "apples", B2:B10) =SUMIF(A2:A10, "=apples", B2:B10) =SUMIF(A2:A10, F1, B2:B10)
Trong đó A2: A10 là danh sách các mục để so sánh với giá trị trong F1.
Ghi chú. Các công thức trên ngụ ý rằng tiêu chí phù hợp với toàn bộ nội dung ô. Do đó, hàm SUMIF sẽ cộng lại Apples đã bán để tính tổng các kết quả phù hợp từng phần, hãy tạo ” nếu ô chứa“tiêu chí như thế này Công thức ký tự đại diện SUMIF.
Ví dụ 3. SUMIF không bằng
Khi một giá trị, văn bản hoặc số, được mã hóa cứng trong tiêu chí, hãy nhớ đặt toàn bộ cấu trúc bằng dấu ngoặc kép.
Ví dụ, để tính tổng số tiền với lô hàng khác 3 ngày, công thức như sau:
Ví dụ: đây là cách bạn có thể tính tổng doanh số bán hàng cho tất cả các khu vực, tức là trong đó cột B không trống:
Để tính tổng các ô trong một số cột nếu ô tương ứng trong một cột khác trống, hãy sử dụng một trong các tiêu chí sau:
“” – tính tổng các ô nếu một ô khác trống; các ô chứa chuỗi trống cũng được coi là trống.
“=” – để tính tổng các ô hoàn toàn trống.
Trong bảng mẫu của chúng tôi, công thức sau sẽ tính tổng doanh số bán hàng cho các vùng không xác định, tức là ô trong cột B trống:
Ví dụ 6. Ký tự đại diện SUMIF trong Excel
=SUMIF(B2:B10, "", C2:D10)
Để tính tổng các ô dựa trên trận đấu từng phần, hãy bao gồm một trong các ký tự đại diện sau trong tiêu chí của bạn:
Dấu hỏi (?) Để thay thế bất kỳ ký tự đơn nào.
Dấu hoa thị
khu vực: Tổng doanh số cho tất cả các khu vực phía North, bao gồm North ,North-East và North-West
, hãy đặt một dấu hoa thị ngay sau văn bản:
=SUMIF(B2:B10, "north", C2:D10) =SUMIF(B2:B10, "north*", C2:D10)
Công thức ký tự đại diện SUMIF để tính tổng nếu ô chứa Để phù hợp với một dấu chấm hỏi chữ hoặc là dấu hoa thị
, đặt dấu ngã (~) trước ký tự, ví dụ: “~?” hoặc “~ *”.
=SUMIF(B2:B10, F1&"*", C2:D10)
Ví dụ: để tổng hợp doanh số cho các khu vực được đánh dấu *, hãy sử dụng “* ~ *” cho tiêu chí. Trong trường hợp này, dấu hoa thị đầu tiên là ký tự đại diện và dấu hoa thị thứ hai là ký tự dấu hoa thị theo nghĩa đen:
Ví dụ 7. Hàm SUMIF trong Excel với ngày tháng Việc sử dụng ngày làm tiêu chí SUMIF rất giống với việc sử dụng số. Điều quan trọng nhất là cung cấp ngày ở định dạng mà Excel hiểu được. Nếu bạn không chắc định dạng ngày nào được hỗ trợ và định dạng nào không, Hàm DATE có thể là một giải pháp.
=SUMIF(B2:B10, "*~*", C2:D10)
Giả sử bạn đang muốn tính tổng doanh số cho các mặt hàng được giao trước ngày 10 tháng 9 năm 2020, tiêu chí có thể được thể hiện theo cách sau:
=SUMIF(B2:B10, "*"&"~"&F1, C2:D10)
hoặc là
SUMIF sử dụng ngày làm tiêu chí Để tính tổng các ô dựa trên ngày hôm nay , bao gồm cái TODAY
=SUMIF(C2:C10, "<9/10/2020", B2:B10) =SUMIF(C2:C10, "<"&DATE(2020,9,10), B2:B10)
Tính tổng các ô sử dụng ngày hôm nay cho tiêu chí Tổng hợp trong một phạm vi ngày , bạn cần xác định ngày nhỏ hơn và lớn hơn một cách riêng biệt. Điều này có thể được thực hiện với sự trợ giúp của SUMIFS
chức năng hỗ trợ nhiều tiêu chí. Ví dụ, để tính tổng nếu một ngày là giữa hai ngày
=SUMIF(C2:C10, "<"&F1, B2:B10)
, đây là công thức để sử dụng:
=SUMIF(C2:C10, "<"&TODAY(), B2:B10)
SUMIF giữa hai ngày
Công thức SUMIF phân biệt chữ hoa chữ thường trong Excel Như đã đề cập, hàm SUMIF trong Excel là trường hợp không nhạy cảm theo tự nhiên. Để tính tổng các ô có điều kiện xem xét chữ hoa, bạn sẽ phải sử dụng một vài hàm khác, cụ thể là hàm SUMPRODUCT và EXACT xử lý chữ hoa và chữ thường là các ký tự khác nhau: SUMPRODUCT (- (CHÍNH XÁC (phạm vi ,tiêu chí )),sum_range) Giả sử bạn có một danh sách các mã mặt hàng trong cột A. Trong đó chữ hoa và chữ thường xác định các mặt hàng khác nhau. Mục tiêu của bạn là tính tổng các số trong cột B cho một mục cụ thể, chẳng hạn A-01.
Để hoàn tất, bạn có thể nhập trực tiếp mục đích vào 2 nd
đối số của hàm EXACT:
Công thức này hoạt động như thế nào:
=SUMPRODUCT(--(EXACT(A2:A10, "A-01")), B2:B10)
Ở trung tâm của công thức, hàm EXACT so sánh mục đích với từng mục trong danh sách nguồn và trả về TRUE. Nếu tìm thấy kết quả khớp chính xác, nếu không thì là FALSE: A toán tử kép một ngôi
(-) chuyển đổi TRUE và FALSE thành 1 và 0, tương ứng:
=SUMPRODUCT(--(EXACT(A2:A10, E1)), B2:B10)
Hàm SUMPRODUCT nhân các phần tử của mảng trên với các mục tương ứng trong B2: B10:
Và bởi vì nhân với 0 cho không, chỉ những mục mà EXACT trả về TRUE (1) mới tồn tại:
Cuối cùng, SUMPRODUCT cộng các sản phẩm và xuất ra tổng.
SUMPRODUCT({1;0;0;0;0;0;1;0;0}, {250;155;130;255;160;280;170;285;110}) SUMPRODUCT({250;0;0;0;0;0;170;0;0})
Cập nhật thông tin chi tiết về Cách Sử Dụng Hàm Sumifs Và Sumif Với Nhiều Điều Kiện trên website Utly.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!