Kết Hợp Hàm Sum Và If Trong Công Thức Mảng ( 22/08/2018)

Kết hợp hàm Sum và If trong công thức mảng

Khi bạn muốn tính tổng có điều kiện cho một tiêu chí riêng lẻ thì đơn giản bạn có thể dùng hàm SUMIF() hoặc cho nhiều tiêu chí thì bạn có thể dùng hàm SUMIFS(). Tuy nhiên, tiêu chí mà bạn có thể sử dụng với hàm trang tính SUMIF() hoặc SUMIFS() được giới hạn theo văn bản, số hoặc phạm vi và không được sử dụng các hằng số mảng trong hàm, không được sử dụng hàm trang tính SUMIF() cùng với các toán tử logic Boolean như OR và AND và dữ liệu phải được bố trí theo dạng cột, không áp dụng được cho điều kiện ở dạng dòng.

Khi bạn muốn sử dụng nhiều điều kiện, hằng số mảng, toán tử Boolean hoặc câu lệnh IF được lồng vào nhau hoặc bạn muốn sử dụng một tổng cho nhiều điều kiện (tiêu chí), hãy sử dụng kết hợp hai hàm SUM và IF. Ví dụ: sử dụng SUM(IF()) thay cho hàm SUMIF() và SUMIFS().

Để sử dụng kết hợp hàm trang tính SUM và IF để tính tổng nhiều tiêu chí, bạn hãy tham khảo các ví dụ sau:

Ví dụ 1: Hàm IF được lồng cùng với toán tử Boolean OR (+)

1. Khởi động Excel và tạo trang tính sau:

A1: Bộ phận B1: Nhân viên C1: Tiêu chí A2: A B2: 2 C2: A A3: B B3: 4 C3: B A4: C B4: 3 A5: A B5: 3 A6: B B6: 3 A7: C B7: 2 A8: A B8: 4 A9: C B9: 3

2. Trong ô D1, nhập công thức sau: =SUM(IF((A2:A9=”A”)+(A2:A9=”B”),B2:B9,0))

3. Nhấn CTRL+SHIFT+ENTER (hoặc COMMAND+RETURN trên máy Mac) để nhập công thức dưới dạng công thức mảng.

Công thức trả về giá trị bằng 16, là tổng số nhân viên tại bộ phận A và B.

Ví dụ 2: Hàm IF được lồng kết hợp với Mảng Tiêu chí ({“A”,”B”})

1. Lặp lại bước 1 từ Ví dụ 1.

2. Trong ô D2, nhập công thức sau: =SUM(IF(A2:A9={“A”,”B”},B2:B9,0))

3. Nhấn CTRL+SHIFT+ENTER (hoặc COMMAND+RETURN trên máy Mac) để nhập công thức dưới dạng công thức mảng.

Công thức lại một lần nữa trả về giá trị bằng 16.

Ví dụ 3: Hàm Trang tính SUMIF()

1. Lặp lại bước 1 từ Ví dụ 1.

2. Trong ô D3, nhập công thức sau: =SUMIF(A2:A9,C2:C3,B2:B9)

3. Nhấn CTRL+SHIFT+ENTER (hoặc COMMAND+RETURN trên máy Mac) để nhập công thức. Công thức trả về giá trị bằng 9, là số nhân viên đáp ứng tiêu chí A.

4. Kéo móc điều khiển điền và điền công thức xuống dưới ô D4.

Công thức trả về giá trị bằng 7, là số nhân viên đáp ứng tiêu chí B.

3 ví dụ trên cho thấy khi bạn ứng dụng thành thạo công thức mảng thì sẽ tiết kiệm rất nhiều thời gian trong việc truy xuất dữ liệu hay tính tổng trong một vùng. Tương tự bạn cũng có thể ứng dụng tính tổng đa điều kiện khi điều kiện được trình bày ở dạng dòng.

 

Tweet