Áp dụng hàm SUMIF và SUMIFS để tính tổng với nhiều điều kiện

Công thức SUMIF – Giải thích cách sử dụng:

Hàm SUMIF cho phép bạn tính tổng các giá trị khi chúng đáp ứng một yếu tố nhất định. Hàm này đã được giải thích cặn kẽ trong một bài viết khác, vậy nên chỉ xin được tổng kết ngắn gọn ở đây.

SUMIF (range, criteria, [sum_range])

  • range – phạm vi các ô cần xem xét theo điều kiện đề ra, yêu cầu phải có.
  • criteria – quy tắc để áp dụng, là yếu tố thiết yếu.
  • sum_range – phạm vi ô được cộng dồn khi thỏa mãn điều kiện, không bắt buộc.

Bạn có thể thấy, công thức hàm SUMIF trong Excel chỉ cho phép bạn sử dụng một điều kiện duy nhất. Mặc dù thế, chúng tôi đã từng nhắc đến việc sử dụng hàm SUMIF với nhiều điều kiện. Làm sao có thể? Đơn giản là bằng cách kết hợp kết quả của nhiều hàm SUMIF và dùng công thức SUMIF với các điều kiện ở dạng mảng, cụ thể là qua ví dụ sau đây.

Hàm SUMIFS – Cú pháp và ứng dụng:

Hàm SUMIFS trong Excel giúp bạn thực hiện việc tổng hợp số liệu dựa vào hàng loạt điều kiện. Hàm SUMIFS đã được cài đặt sẵn từ phiên bản Excel 2007, do đó bạn có thể áp dụng nó trong các phiên bản 2013, 2010 và 2007.

Cú pháp của SUMIFS hơi phức tạp hơn so với SUMIF:

SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Ba thành phần đầu tiên là bắt buộc, còn phạm vi và các điều kiện khác thì tùy bạn thêm vào.

  • sum_range – ô hoặc nhóm ô cần được tổng hợp, yếu tố thiết yếu. Có thể là một ô, một chuỗi ô hay một chuỗi có tên định sẵn. Chỉ tính tổng các ô chứa số liệu; ô trống và chứa văn bản sẽ không được tính.
  • criteria_range1 – phạm vi đầu tiên cần được xem xét theo tiêu chuẩn đã đặt, yêu cầu cần có.
  • criteria1 – tiêu chuẩn đầu tiên cần áp dụng cho phạm vi ô, không thể thiếu. Các tiêu chuẩn có thể mang dạng số, biểu thức lô-gic, tham chiếu ô, chữ, hoặc có thể là một hàm khác trong Excel. Ví dụ, sử dụng tiêu chuẩn như 10, “>=10”, A1, “cherries” hay TODAY().
  • criteria_range2, criteria2, … – các phạm vi và tiêu chuẩn liên quan đến chúng được thêm vào theo nhu cầu của bạn. Bạn có thể dùng lên tới 127 phạm vi/tiêu chuẩn khi tạo công thức SUMIFS.

Lưu ý: Hàm SUMIFS sử dụng phép lô-gic AND, có nghĩa là chỉ những ô nào đáp ứng đầy đủ các điều kiện được liệt kê mới được tính vào tổng.

Hãy nhìn vào cách hàm SUMIFS hoạt động với hai điều kiện. Giả sử bạn có một bảng kê danh sách các lô hàng trái cây từ nhiều nhà cung cấp. Cột A chứa tên loại quả, cột B chứa tên nhà cung cấp và cột C chứa số lượng. Đặt ra trường hợp, bạn muốn tính tổng số lượng các quả táo (apples) mà Pete cung cấp.

Data to be summed with two conditions

Khi tiếp cận một khái niệm mới, hãy bắt đầu từ những bước cơ bản. Vậy, để thực hiện, chúng ta hãy xác định các đối số cần thiết cho công thức SUMIFS:

  • sum_range – C2:C9
  • criteria_range1 – A2:A9
  • criteria1 – “apples”
  • criteria_range2 – B2:B9
  • criteria2 – “Pete”

Bây giờ, hãy tiến hành

Kết hợp lại các yếu tố đã nêu, công thức SUMIFS mà bạn sẽ thu được là:

= SUMIFS (C2: C9, A2: A9, "táo", B2: B9, "Pete")

Ví dụ về công thức Excel SUMIFS với hai điều kiện

Để việc chỉnh sửa công thức trở nên thuận tiện hơn, có thể thay thế các chuỗi văn bản “táo” và “Pete” bằng các tham chiếu đến các ô. Bằng cách đó, không cần phải can thiệp vào công thức mỗi khi cần tính tổng số lượng trái cây khác từ một nhà cung cấp khác:

= SUMIFS (C2: C9, A2: A9, F1, B2: B9, F2)

Những Lưu Ý Khi Sử Dụng SUMIFS và SUMIF Trong Excel:

Do mục tiêu của bài hướng dẫn này là bao quát tất cả những phương pháp có thể để tính tổng các giá trị dựa theo nhiều tiêu chí, chúng ta sẽ đề cập đến các ví dụ sử dụng cả hai hàm SUMIFS và SUMIF. Để áp dụng chính xác, bạn cần hiểu rõ điểm tương đồng và khác biệt chính giữa hai hàm này.

Phổ thông thì tính chất chung giữa chúng khá minh bạch – mục đích cuối cùng tương tự và đều yêu cầu tham số nhất định – nhưng sự khác biệt, mặc dù không rõ ràng lắm, lại vô cùng quan trọng.

1. Sắp xếp thứ tự của các đối số

Trong hàm SUMIF và SUMIFS của Excel, vị trí của các đối số khác biệt rõ ràng. Ví dụ như trong SUMIFS, sum_range được xếp ở vị trí đầu tiên, nhưng trong SUMIF thì lại nằm ở vị trí thứ ba.

Dường như Microsoft đã có chủ ý khiến cho việc học và sử dụng trở nên phức tạp hơn, nhưng nếu nhìn sâu vào, lý do thực sự lại rất hợp lý. Trong SUMIF, sum_range không bắt buộc phải cùng kích thước với range, và bạn hoàn toàn có thể loại bỏ nó. Nếu bạn không đưa nó vào, công thức SUMIF của bạn vẫn sẽ tính tổng giá trị trong khoảng được chỉ định (đối số range là tham số đầu tiên).

Với SUMIFS thì khác, sum_range là yếu tố quan trọng và bắt buộc phải có, vì thế mới được đặt lên hàng đầu. Có lẽ, Microsoft nghĩ rằng sau khi bạn đã thêm hàng chục hay hàng trăm điều kiện vào, ai đó có thể vô tình bỏ sót việc xác định dãy dữ liệu cần tính tổng 🙂

Nói ngắn gọn, khi bạn chép và chỉnh sửa các hàm này, hãy chắc chắn rằng bạn đã đặt các đối số theo thứ tự chính xác.

2. Kích thước của sum_range và criteria_range

Trong hàm SUMIF, sum_range không cần thiết phải có kích thước giống hệt với range, miễn là bạn đang xem xét đến ô trên cùng bên trái. Ngược lại, trong hàm SUMIFS, mỗi criteria_range cần phải có cùng số hàng và cột với sum_range.

Theo đó, công thức = SUMIF (A2: A9, F1, C2: C18) sẽ cho kết quả chính xác bởi vì Excel chỉ cần nhận biết ô đầu tiên bên trái trong sum_range (vẫn là C2 như trong ví dụ) và sau đó lấy cả số lượng hàng và cột tương ứng với range.

Tuy nhiên, công thức SUMIFS:  = SUMIFS (C2: C9, A2: A9, "táo", B2: B10, "Pete") sẽ hiển thị lỗi #VALUE! do criter_range2 (B2: B10) không khớp với criteria_range1 (A2: A9) sum_range (C2: C9).

Chúng ta có vẻ như đã qua phần lý thuyết, hãy tiến tới các bước thực hành thông qua các ví dụ cụ thể sau đây:

Cách sử dụng SUMIFS trong Excel – các ví dụ công thức

Ở phần trên, chúng ta đã bàn về một công thức SUMIFS đơn giản sử dụng hai điều kiện văn bản. Tương tự, bạn cũng có thể triển khai SUMIFS trong Excel với các điều kiện nhiều hơn, biểu diễn qua các số, ngày, biểu thức logic, và sử dụng thêm các hàm khác trong Excel.

Ví dụ 1: Công thức SUMIFS áp dụng toán tử so sánh

Xét một bảng cung cấp trái cây, bạn muốn tính tổng tất cả số lượng trái cây mà Mike cung cấp với số lượng lớn hơn hoặc bằng 200. Ở đây, bạn cần dùng đến toán tử “lớn hơn hoặc bằng” (>=) trong điều kiện thứ hai và từ đó xây dựng công thức SUMIFS.

= SUMIFS(C2:C9, B2:B9, “Mike”, C2:C9, “>= 200”)

Công thức SUMIFS trong Excel với toán tử so sánh

Xin chú ý: Khi bạn sử dụng hàm SUMIFS, bạn phải nhớ rằng những biểu thức điều kiện chứa các toán tử so sánh phải được bao quanh bởi dấu ngoặc kép (“”).

Các toán tử so sánh mà chúng tôi đã giới thiệu trong bài viết về hàm SUMIF có thể áp dụng tương tự cho các điều kiện trong SUMIFS. Ví dụ, để tính tổng của những giá trị trong cột C từ C2 đến C9 mà lớn hơn hoặc bằng 200 và không vượt quá 300, bạn có thể sử dụng công thức sau:

= SUMIFS(C2:C9, C2:C9, “>= 200”, C2:C9, “<= 300”)

Ví dụ 2: Công thức SUMIFS kết hợp với ngày tháng

Nếu bạn cần tính tổng giá trị của các mục với nhiều điều kiện dựa vào ngày tháng cụ thể, hãy sử dụng hàm TODAY() như một phần trong điều kiện của hàm SUMIFS. Công thức dưới đây sẽ tính tổng những giá trị trong cột D với điều kiện là các ngày tương ứng trong cột C thuộc khoảng 7 ngày trước đến ngày hiện tại:

= SUMIFS(D2:D10, C2:C10, “>=” & TODAY() – 7, C2:C10, “<=” & TODAY())

Công thức SUMIFS trong Excel cho ngày tháng

Ghi chú: Khi dùng các hàm của Excel kết hợp với toán tử logic trong điều kiện, bạn nên dùng dấu & để nối chúng thành một chuỗi, ví dụ như “>=” & TODAY().

Bạn cũng có thể sử dụng hàm SUMIF để tính tổng giá trị trong dải ngày cụ thể. Công thức SUMIFS dưới đây sẽ cộng dồn giá trị của ô từ C2 đến C9 nếu ngày trong cột B rơi vào khoảng từ ngày 1 tháng 10 năm 2014 đến ngày 31 tháng 10 năm 2014:

= SUMIFS(C2:C9, B2:B9, “>= 10/1/2014”, B2:B9, “<= 10/31/2014”)

Bạn có thể nhận được kết quả tương tự thông qua việc tính sự chênh lệch giữa hai hàm SUMIF, như ví dụ đã cho thấy. Nhưng công thức SUMIFS được xem là dễ áp dụng và dễ hiểu hơn nhiều, đúng không?

Ví dụ 3: Hàm SUMIFS với các ô rỗng và không rỗng

Khi bạn phải phân tích dữ liệu và báo cáo, việc tổng hợp thông tin dựa vào các ô rỗng hoặc đã được điền dữ liệu là rất cần thiết.

Điều kiện Mô tả Công thức mẫu
Các ô trống “=” Cộng dồn giá trị mà ô tương ứng không chứa dữ liệu nào (không có công thức, không có ký tự) =SUMIFS(C2:C10, A2:A10, “=”, B2:B10, “=”)

Tính tổng các giá trị trong phạm vi C2:C10 nếu ô tương ứng trong cột A và B rỗng hẳn.

“” Tính tổng giá trị mà các ô tương ứng trực quan trông như trống, bao gồm cả chuỗi rỗng do một số hàm Excel khác trả về (ví dụ: ô chứa công thức = “”). =SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”)

Tính tổng các giá trị trong phạm vi C2:C10 (với cùng điều kiện như đã nêu)

Các ô không trống “<>” Cộng dồn các giá trị có ô tương ứng đã được điền dữ liệu, kể cả ô chứa chuỗi có độ dài bằng 0 =SUMIFS(C2:C10, A2:A10, “<>”, B2:B10, “<>”)

Tính tổng các giá trị trong phạm vi C2:C10 nếu ô tương ứng trong cả cột A và B đã được điền dữ liệu.

SUM-SUMIF
hoặc
SUM / LEN Tính tổng giá trị của các ô có ô tương ứng không phải là ô trống, loại trừ những ô có chuỗi độ dài bằng không =SUM(C2:C10) – SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”)

=SUM((C2:C10) * (LEN(A2:A10)>0) * (LEN(B2:B10)>0))

Xác định tổng giá trị trong phạm vi C2:C10 khi các ô liên quan ở cột A và B không phải là các ô trống và cũng không chứa chuỗi có chiều dài bằng không.

Giờ đây, chúng ta hãy xem xét việc sử dụng hàm SUMIFS với điều kiện “trống” và “không trống” được áp dụng trên dữ liệu thực tế như thế nào:

Xét cho trường hợp bạn sở hữu ngày đặt hàng trong cột B, ngày giao hàng trong cột C và lượng hàng trong cột D. Bạn muốn biết tổng số lượng hàng chưa giao là bao nhiêu? Tức là cần tính tổng những giá trị tại ô không rỗng trong cột B và rỗng trong cột C.

Để giải quyết điều này, bạn có thể áp dụng công thức SUMIFS với hai điều kiện sau:

= SUMIFS(D2:D10, B2:B10, “<>“, C2:C10, “=”)

Công thức Excel SUMIFS cho các ô trống và không trống

 

Ví dụ 4: Cách thiết lập điều kiện với hàm SUMIFS

Yêu cầu: Với bảng dữ liệu trong khoảng F2:H10, cần tính tổng giá trị trong khoảng B4:D7 tuân theo các điều kiện cụ thể ở cột A (Mã) và hàng 3 (Ngày)
Bước 1: Phân tích yêu cầu

  • Yêu cầu đặt ra là tính tổng theo hai điều kiện: Ngày và Mã, do đó không thể sử dụng SUMIF mà phải dùng SUMIFS (trong trường hợp này, chúng ta khước từ các hàm khác và chỉ tập trung vào việc áp dụng SUMIF/SUMIFS)
  • Các giá trị điều kiện không cố định mà có thể thay đổi, cần viết một công thức SUMIFS ở ô B4 và sao chép nó đến các ô còn lại
  • Các điều kiện Mã và Ngày có thể được điều chỉnh linh hoạt trong công thức.

Bước 2: Tạo công thức SUMIFS
Kết cấu của hàm SUMIFS(sum_range, Criteria_range1, Criteria1, …)

  • Sum_range: là cột có chứa Số lượng trong khu vực dữ liệu F2:H10
  • Criteria_range1: là cột có chứa Mã trong khu vực dữ liệu F2:H10
  • Criteria1: là điều kiện liên quan đến Mã (đối sánh với Criteria_range1), ô B4 sẽ tham chiếu đến điều kiện ở ô A4
  • Criteria_range2: là cột có chứa Ngày trong khu vực dữ liệu F2:H10
  • Criteria2: là điều kiện liên quan đến Ngày (đối sánh với Criteria_range2), và tại ô B4 điều kiện sẽ là ô B3

Do đó, công thức cho ô B4 sẽ như sau:

B4=SUMIFS(H3:H10,F3:F10,A4,G3:G10,B3)

Xem thêm: Cách cố định hàng, cố định cột trong Excel
Bước 3: Điều chỉnh điều kiện trong hàm SUMIFS
Khi bạn sao chép công thức sang bên phải (FillRight từ B4 đến D4): cần phải giữ cố định cột A, do đó ô A4 sẽ được cố định thành $A4.

Khi sao chép xuống dưới (FillDown từ B4 đến B7): cần giữ cố định hàng 3, nên ô B3 sẽ được cố định thành B$3.

Khu vực F2:H10 cũng cần được cố định để khi sao chép công thức khu vực này không thay đổi.

Vậy, công thức được xây dựng lại cho B4 sau khi chỉnh sửa sẽ như sau:

Sau khi sao chép công thụ sang các ô khác, kết quả chính xác sẽ được như sau:

* Áp dụng:

Phương pháp này thường được dùng trong việc soạn thảo các báo cáo chi tiết, báo cáo dựa trên nhiều đối tượng khác nhau cùng lúc.

Khi sử dụng hàm SUMIF với nhiều điều kiện OR

Lưu ý từ đầu bài viết, hàm SUMIFS được thiết kế để làm việc với điều kiện AND. Tuy nhiên, nếu bạn cần tính tổng với nhiều điều kiện OR – tức là khi ít nhất một trong các điều kiện đúng – bạn sẽ cần sử dụng phương pháp khác.

Yêu cầu sẽ được thỏa mãn theo những cách dưới đây:

Ví dụ 1. SUMIF cộng SUMIF

Cách đơn giản và hiệu quả nhất để tính tổng là áp dụng nhiều hàm SUMIF. Cụ thể, công thức dưới đây sẽ cho thấy cách tính tổng lượng sản phẩm mà Mike và John đã cung cấp:

= SUMIF (C2: C9, “Mike”, D2: D9) + SUMIF (C2: C9, “John”, D2: D9)

Excel SUMIF formula with multiple OR criteria

Như bạn có thể thấy, hàm SUMIF đầu tiên sẽ chỉ ra số lượng của “Mike”, còn hàm SUMIF thứ hai lại cho biết số lượng của “John”. Cuối cùng, bạn chỉ cần cộng cả hai kết quả lại với nhau.

Ví dụ 2. Tổng của SUM và SUMIF sử dụng tham số mảng

Khi số lượng điều kiện tăng lên, phương pháp trên có thể trở nên phức tạp. Nếu bạn muốn tính tổng giá trị dựa trên nhiều điều kiện OR, việc sử dụng tham số mảng trong hàm SUMIF là cách làm tốt hơn:

Hãy bắt đầu bằng việc liệt kê các điều kiện của bạn với dấu phẩy làm dấu phân cách, và sau đó đặt chúng vào trong {dấu ngoặc nhọn} để tạo thành một mảng.

Với ví dụ đã nêu, nếu bạn muốn cộng tổng sản phẩm từ John, Mike và Pete, mảng điều kiện sẽ là {“John”, “Mike”, “Pete”}, và công thức SUMIF đầy đủ sẽ là: =SUMIF (C2: C9, {“John”, “Mike”, “Pete”}, D2: D9).

Tham số mảng này bao gồm ba giá trị cần thiết cho hàm SUMIF, nhằm tạo ra ba kết quả độc lập, tuy nhiên việc nhập các công thức vào một ô sẽ chỉ hiển thị kết quả của điều kiện đầu tiên – số sản phẩm do John cung cấp. Để thu được kết quả từ tất cả các điều kiện, bạn cần lồng công thức SUMIF vào trong hàm SUM như sau:

= SUM (SUMIF (C2: C9, {“John”, “Mike”, “Pete”}, D2: D9))

Using SUM & SUMIF with an array argument to sum values with multiple OR criteria

Công thức dùng một mảng điều kiện như vừa nêu giúp công thức gọn gàng hơn nhiều so với việc sử dụng SUMIF cộng SUMIF và bạn có thể thoải mái thêm điều kiện vào mảng đó.

Phương pháp này có thể áp dụng với cả số lẫn văn bản. Chẳng hạn, nếu bạn có ID nhà cung cấp thay vì tên ở cột C, như 1, 2, 3… thì công thức SUMIF của bạn sẽ là:

= SUM (SUMIF (C2: C9, {1,2,3}, D2: D9))

Khác với giá trị văn bản, số không cần phải được đặt trong dấu ngoặc kép khi ở trong mảng tham số.

Ví dụ 3. SUMPRODUCT kết hợp SUMIF

Nếu bạn ưa chuộng việc liệt kê điều kiện trong các ô khác nhau và không muốn nhúng trực tiếp chúng vào công thức, bạn có thể dùng hàm SUMPRODUCT với SUMIF để nhân các giá trị của mảng xác định và tính tổng chúng.

= SUMPRODUCT (SUMIF (C2: C9, G2: G4, D2: D9))

Giả sử G2: G4 là vùng chứa điều kiện của bạn, có thể là tên nhà cung cấp, mô tả như trong hình dưới đây:

Nhưng không có gì cản bạn nếu bạn muốn dùng mảng điều kiện trong hàm SUMIF:

= SUMPRODUCT (SUMIF (C2: C9, {“Mike”, “John”, “Pete”}, D2: D9))

Cả hai công thức đều mang lại kết quả như bạn thấy trong hình:

Sum with multiple criteria using the SUMPRODUCT / SUMIF formula

Excel SUMIFS với điều kiện OR đa dạng

Nếu bạn muốn thực hiện cộng tổng có điều kiện cho các giá trị trong Excel thì

Bạn sẽ cần dùng đến hàm SUMIFS thay vì SUMIF để xử lý vài điều kiện nhất định thay vì chỉ là điều kiện OR. Bên dưới là công thức mà chúng ta sắp xem xét.

Hãy cùng xét một dẫn chứng sẽ làm sáng tỏ vấn đề này. Xét tới bảng cung cấp trái cây của chúng tôi, chúng ta sẽ thêm vào cột E là Ngày giao hàng và muốn biết tổng lượng hàng Mike, John và Pete đã giao trong tháng 10.

Ví dụ 1. SUMIFS + SUMIFS

Phương pháp này dù có vẻ phức tạp với các công thức lặp đi lặp lại nhưng thực tế nó khá dễ nắm bắt và quan trọng là nó mang lại kết quả đúng đắn

= SUMIFS (D2: D9, C2: C9, “Mike”, E2: E9, “> = 10/1/2014”, E2: E9, “<= 10/31/2014”) +

SUMIFS (D2: D9, C2: C9, “John”, E2: E9, “> = 10/1/2014”, E2: E9, “<= 10/31/2014”) +

SUMIFS (D2: D9, C2: C9, “Pete”, E2: E9, “> = 10/1/2014”, E2: E9, “<= 10/31/2014”)

Bằng cách tính riêng rẽ theo từng nhà cung cấp với hai điều kiện – sau 1 tháng 10 (“> = 10/1/2014”) và trước 31 tháng 10 (“<= 10/31/2014”), rồi cộng dồn lại, bạn sẽ có được tổng cần tìm.

Thêm nhiều hàm SUMIFS để tính tổng các giá trị với nhiều điều kiện OR

Ví dụ 2. SUM & SUMIFS với đối số mảng

Cách tiếp cận này đã được mô tả qua ví dụ với SUMIF, và giờ chúng ta chỉ cần chép lại công thức đó, điều chỉnh thứ tự các đối số (vì SUMIF và SUMIFS có sự khác biệt) và thêm vào các điều kiện thêm. Công thức này sẽ gọn gàng hơn phương pháp SUMIFS + SUMIFS:

= SUM (SUMIFS (D2: D9, C2: C9, {“Mike”, “John”, “Pete”}, E2: E9, “> = 10/1/2014”, E2: E9, “<= 10 / 31/2014 “))

Kết quả thu được từ công thức này không khác gì hình ảnh minh họa trình bày phía trên.

Ví dụ 3. SUMPRODUCT & SUMIFS

Nhớ lại rằng cách làm với SUMPRODUCT không giống hai phương pháp trước, bởi với mỗi điều kiện bạn sẽ nhập vào một ô riêng lẻ chứ không nhập trực tiếp vào công thức. Trong trường hợp có nhiều điều kiện, bạn cần dùng đến hàm ISNUMBER và hàm MATCH thay vì chỉ dùng SUMPRODUCT.

Giả sử tên các nhà cung cấp được đặt tại ô H1: H3, Ngày bắt đầu ở ô H4 và Ngày kết thúc tại H5, công thức SUMPRODUCT của bạn sẽ ra sao:

= SUMPRODUCT (– (E2: E9> = H4), – (E2: E9 <= H5), — (ISNUMBER (MATCH (C2: C9, H1: H3,0)), D2: D9)

Nhiều người thắc mắc về việc sử dụng hai dấu trừ (–) trong công thức SUMPRODUCT. Lý do đằng sau đây chính là SUMPRODUCT không tính các giá trị số khiến cho các phép so sánh trong công thức trả về giá trị Boolean (TRUE/FALSE) thay vì các số. Chúng ta dùng dấu trừ để biến các giá trị Boolean thành 1 hoặc 0. Toán tử đơn vị xử lý đầu tiên biến TRUE/FALSE thành -1/0 tương ứng, rồi toán tử thứ hai chuyển nó thành +1 và 0 làm cho SUMPRODUCT có thể xử lý chúng.

Hy vọng rằng giải thích này là có ích đối với bạn. Và dù bạn có hiểu hay không thì bạn chỉ cần nhớ rằng – dùng toán tử đôi (–) khi sử dụng các phép so sánh trong công thức SUMPRODUCT là cần thiết.

Bí quyết dùng hàm SUM với công thức mảng

Có thông tin rằng Microsoft đã giới thiệu hàm SUMIFS trong Excel 2007. Nếu ai đang dùng Excel 2003, 2000 hoặc các phiên bản cũ hơn thì…

Nếu bạn muốn thêm các số hạng với nhiều điều kiện AND trong phiên bản Excel trước 2007, bạn sẽ cần dùng đến công thức tổng mảng SUM. Cách làm này cũng có thể áp dụng được cho phiên bản Excel mới từ 2013 đến 2007, dù rằng hàm SUMIFS có thể được xem là cách làm mới hơn.

Trong khi công thức SUMIF mà chúng ta đã bàn luận sử dụng đối số kiểu mảng, thì một công thức mảng lại là một khái niệm khác hẳn.

Ví dụ 1: Tính tổng với điều kiện AND trong Excel phiên bản 2003 và các phiên bản trước:

Ta cùng nhìn lại ví dụ ban đầu khi chúng ta cần tính tổng số lượng của loại trái cây và từ nhà cung cấp cụ thể:

Data to be summed with two conditions

Bình thường việc này có thể dễ dàng được hoàn thành bằng công thức SUMIFS như sau:

= SUMIFS (C2: C9, A2: A9, “apples”, B2: B9, “Pete”)

Nhưng chúng ta hãy xem xét cách làm tương tự có thể được thực hiện ra sao khi không có SUMIFS trong Excel. Bạn sẽ liệt kê mọi điều kiện cần được thỏa mãn với định dạng = “condition”. Trong ví dụ này, ta có hai điều kiện sau:

Điều kiện 1: A2: A9 cho “apples”

Điều kiện 2: B2: B9 cho “Pete”

Tiếp theo, bạn sẽ sử dụng một công thức SUM và nhân tất cả các điều kiện với nhau: mỗi điều kiện được bao bọc bởi dấu ngoặc đơn, và số lượng cần tính tổng ở cuối cùng là C2: C9:

= SUM ((A2: A9 = “apples”) * (B2: B9 = “Pete”) * (C2: C9))

Minh họa dưới đây cho thấy công thức này hoạt động rất hiệu quả kể cả trong phiên bản Excel 2013 mới nhất.

An example of using Excel SUM in an array formula

Lưu ý: Khi bạn nhập bất kỳ công thức mảng nào, bạn cần nhấn tổ hợp phím Ctrl + Shift + Enter để công thức được đặt trong {dấu ngoặc nhọn} – biểu thị rằng đây là một công thức mảng đã nhập chính xác. Nếu bạn thử nhập dấu ngoặc nhọn bằng tay, công thức sẽ chỉ là chuỗi văn bản và không hoạt động được.

Ví dụ 2: Công thức mảng SUM trong các phiên bản Excel mới

Trong những phiên bản Excel mới như 2013, 2010 hoặc 2007, bạn không nên đánh giá thấp tính năng của hàm SUM. Sử dụng công thức mảng SUM không chỉ giúp huấn luyện tư duy mà còn hữu ích trong thực tiễn, ví dụ như sau:

Hãy tưởng tượng bạn có hai cột B và C, bạn muốn biết cột C lớn hơn cột B bao nhiêu lần khi các giá trị của cột C từ 10 trở lên. Bạn có thể giải bài toán bằng cách dùng công thức mảng SUM:

= SUM ((C1: C10 >= 10) * (C1: C10 > B1: B10))

Using SUM array formulas in modern Excel versions

Bạn có thể chưa nhận thấy công thức trên có ích thế nào? Hãy xem xét một cách khác đi:

Giả sử bạn đang có một danh sách đơn hàng như trong hình minh họa dưới đây và bạn muốn biết có bao nhiêu sản phẩm chưa được giao đủ trong một ngày cụ thể. Ta có các điều kiện sau theo cách diễn đạt của Excel:

  • Điều kiện 1: Giá trị trong cột B (Ordered items) phải lớn hơn không
  • Điều kiện 2: Giá trị trong cột C (Delivered) cần thấp hơn cột B
  • Điều kiện 3: Ngày trong cột D (Due date) phải trước 11/1/2014.

Khi kết hợp ba điều kiện/range, ta sẽ có công thức dưới đây:

= SUM ((B2: B10 >= 0) * (B2: B10 > C2: C10) * (D2: D10 < G2))

Ví dụ minh họa công thức tính tổng mảng trong Excel 2013

Những ví dụ về công thức được đề cập trong bài viết này chỉ là bề nổi của tảng băng về hàm SUMIFS và SUMIF. Mong rằng, những ví dụ này đã cung cấp cho bạn những gợi ý đầu tiên nhằm giúp bạn tính toán tổng các giá trị trong bảng tính Excel một cách dễ dàng hơn, ngay cả khi bạn cần xem xét nhiều điều kiện phức tạp.

Những lưu ý khác khi áp dụng hàm Sumifs

  • Khi sử dụng hàm SUMIFS, bạn có thể thêm tới 127 điều kiện hoặc tiêu chí cùng một lúc. Dù vậy, việc thêm quá nhiều tiêu chí có thể gây sai lệch, vậy nên hãy cố gắng giữ cho công thức được gọn gàng.
  • Trong trường hợp các ô trong dãy tiêu chí không được điền, Excel sẽ trả lại thông báo “không thực hiện được”. Do đó, để đảm bảo nhận kết quả đúng, hãy chắc chắn rằng bạn đã điền đầy đủ thông tin ở mỗi dãy tiêu chí, nhất là vùng dãy tiêu chí.
  • Bạn cũng có thể sử dụng các kí tự đại diện trong hàm SUMIFS. Chẳng hạn, dùng “?” để thay thế cho một ký tự bất kỳ và “*” để thế chỗ cho một chuỗi ký tự sau một nhóm chữ cái cụ thể.

Bên cạnh hàm SumIf và SumIfs, chúng ta còn có thể sử dụng các hàm khác như SUMPRODUCT để thực hiện phép tính tổng với nhiều tiêu chí. Tùy vào yêu cầu tính toán cụ thể và đặc tính của dữ liệu mà bạn sẽ xem xét hàm nào sẽ phù hợp nhất cho mình. Chính vì thế, việc nâng cao hiểu biết về các loại hàm và cách tổ chức dữ liệu sẽ hỗ trợ đắc lực trong công việc của bạn.

Xem thêm: Hướng dẫn chi tiết cách sử dụng hàm SUMIF trong Excel 2021 – các công thức tiện ích Dễ Hiểu

Cuối cùng, việc kết hợp linh hoạt các công cụ của Excel như định dạng dữ liệu, định dạng theo điều kiện, tách dữ liệu, phân loại, và tạo báo cáo với Pivot Table, vẽ đồ thị… sẽ thúc đẩy đáng kể hiệu quả công việc của bạn. Tất cả những kiến thức này đều có thể được học trong khóa học Excel từ cơ bản đến nâng cao cho người đi làm

“`

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop