SUMIFS
Hàm SUMIFS trả về tổng các ô trong một tập hợp trong đó các giá trị kiểm tra đáp ứng các điều kiện cho trước.
SUMIFS(giá trị tổng; giá trị kiểm tra; điều kiện; giá trị kiểm tra…; điều kiện…)
giá trị tổng: Một tập hợp chứa các giá trị sẽ được cộng. giá trị tổng có thể chứa giá trị số, giá trị ngày/giờ hoặc giá trị khoảng thời gian.
giá trị kiểm tra: Một tập hợp chứa các giá trị sẽ được kiểm tra. giá trị kiểm tra có thể chứa bất kỳ giá trị nào.
điều kiện: Một biểu thức so sánh hoặc kiểm tra các giá trị và kết quả trong giá trị lô-gic TRUE hoặc FALSE. điều kiện có thể bao gồm các toán tử so sánh, hằng số, toán tử ghép nối và, tham chiếu và ký tự đại diện. Bạn có thể sử dụng ký tự đại diện để khớp với bất kỳ ký tự duy nhất nào hoặc nhiều ký tự trong biểu thức. Bạn có thể sử dụng dấu ? (dấu hỏi chấm) để biểu thị một ký tự, dấu * (dấu hoa thị) để biểu thị nhiều ký tự, và dấu ~ (dấu ngã) để biểu thị rằng ký tự tiếp sau đó cần phải khớp, chứ không phải được dùng làm ký tự đại diện. điều kiện cũng có thể chứa một hàm REGEX thay vì ký tự đại diện.
giá trị kiểm tra…: Tùy ý bao gồm một hoặc nhiều tập hợp chứa các giá trị sẽ được kiểm tra. Mỗi tập hợp giá trị kiểm tra phải được theo sau bởi biểu thức điều kiện. Mẫu giá trị kiểm tra, điều kiện này có thể được lặp lại nhiều lần nếu cần.
điều kiện…: Nếu tập hợp tùy chọn của giá trị kiểm tra được bao gồm, điều kiện… là một biểu thức bổ sung dẫn đến giá trị lô-gic TRUE hoặc FALSE. Phải có một điều kiện theo sau mỗi tập hợp giá trị kiểm tra; do đó, số các đối số của hàm này luôn là số lẻ.
Ghi chú
Đối với mỗi cặp giá trị kiểm tra và điều kiện, ô hoặc giá trị tương ứng (có cùng vị trí trong tập hợp) sẽ được so sánh với điều kiện. Nếu đáp ứng tất cả các điều kiện thì ô hoặc giá trị tương ứng trong giá trị tổng sẽ được đưa vào tổng.
Tất cả các tập hợp phải có cùng kích thước.
Ví dụ |
|---|
Cho bảng sau: |
A | B | C | |
|---|---|---|---|
1 | Tấn | Xếp hạng | Ngày |
2 | 6 | 1 | 10/12/2012 |
3 | 15 | 2 | 10/12/2012 |
4 | 5 | 1 | 13/12/2012 |
5 | 7 | 2 | 13/12/2012 |
6 | 8 | 2 | 14/12/2012 |
7 | 6 | 1 | 15/12/2012 |
8 | 7 | 2 | 15/12/2012 |
9 | 4 | 2 | 16/12/2012 |
10 | 7 | 1 | 16/12/2012 |
11 | 8 | 2 | 16/12/2012 |
12 | 5 | 1 | 17/12/2012 |
13 | 11 | 2 | 20/12/2012 |
=SUMIFS(A2:A13; B2:B13; "=1"; C2:C13; ">=13/12/2012"; C2:C13; "<=17/12/2012") trả về 23, là số tấn hàng được giao trong tuần 17 tháng 12 đã được xếp hạng 1. Tập hợp A2:A13 có chứa số tấn, là giá trị sẽ được tính tổng (giá trị tổng). Tập hợp B2:B13 (giá trị kiểm tra) có chứa giá trị đầu tiên sẽ được kiểm tra cho xếp hạng 1 (điều kiện). Tập hợp C2:C13 (giá trị kiểm tra…) có chứa giá trị thứ hai sẽ được kiểm tra để tìm kết quả phù hợp cho ngày bắt đầu (điều kiện…). Dãy tương tự cũng chứa giá trị thứ ba sẽ được kiểm tra, ngày kết thúc. Hàng 4 (5 tấn), 7 (6 tấn), 10 (7 tấn), và 12 (5 tấn) thỏa mãn tất cả các điều kiện và tổng của những hàng này là 23 tấn. Bạn cũng có thể sử dụng các toán tử ≥ và ≤. =SUMIFS(A2:A13; B2:B13; "=2"; C2:C13; ">=13/12/2012"; C2:C13; "<=17/12/2012") trả về 34, số tấn hàng được giao trong cùng tuần đã được xếp hạng "2". Bạn cũng có thể sử dụng các toán tử ≥ và ≤. =SUMIFS(A2:A13; B2:B13; "<>2"; C2:C13; ">=10/12/2012"; C2:C13; "<=20/12/2012") trả về 29, số tấn hàng được giao trong bất kỳ tuần nào không được xếp hạng 2. Bạn cũng có thể sử dụng các toán tử ≠, ≥ và ≤. |
Ví dụ sử dụng REGEX |
|---|
Cho bảng sau: |
A | B | |
|---|---|---|
1 | 45 | marina@example.com |
2 | 41 | Aaron |
3 | 29 | michael@example.com |
4 | 64 | jake@example.com |
5 | 12 | Sarah |
=SUMIFS(A1:A5; B1:B5;REGEX("([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})",); A1:A5; ">10") trả về 138, là tổng của các ô trong A1:A5 lớn hơn 10 và trong đó ô tương ứng trong B1:B5 chứa một địa chỉ email. |