SUMIFS (Hàm SUMIFS)

Bài viết này mô tả cú pháp công thức và cách dùng hàm (hàm: Một công thức viết sẵn, lấy một hoặc nhiều giá trị, thực hiện một thao tác, và trả về một hoặc nhiều giá trị. Dùng hàm để đơn giản hóa và rút ngắn các công thức trên trang tính, đặc biệt là khi những công thức đó thực hiện các phép tính dài hoặc phức tạp).SUMIFS trong Microsoft Excel.

Mô tả

Thêm các ô trong một phạm vi (phạm vi: Hai ô trở lên trong trang tính. Các ô trong phạm vi có thể liền kề hoặc không liền kề.) đáp ứng nhiều tiêu chí. Ví dụ, nếu bạn muốn cộng các số trong phạm vi A1:A20 chỉ khi các số tương ứng trong B1:B20 lớn hơn không (0) và các số tương ứng trong C1:C20 nhỏ hơn 10, bạn có thể dùng công thức sau đây:

=SUMIFS(A1:A20, B1:B20, ">0", C1:C20, "<10")

 Quan trọng   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 trong hàm SUMIFS, nhưng nó là đối số thứ ba trong hàm SUMIF. Nếu bạn sao chép và sửa những hàm tương tự như nhau này, hãy bảo đảm bạn đặt các đối số theo đúng thứ tự.

Cú pháp

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

Cú pháp hàm SUMIFS có các đối số (tham đối: Giá trị cung cấp thông tin về một hành động, sự kiện, phương pháp, thuộc tính, hàm hoặc thủ tục). sau đây:

  • Sum_range    Bắt buộc. Một hoặc nhiều ô cần cộng tổng, bao gồm các số hoặc tên, phạm vi, hoặc tham chiếu ô (tham chiếu ô: Bộ các tọa độ mà ô chiếm giữ trên trang tính. Ví dụ, tham chiếu ô xuất hiện ở phần giao cắt giữa cột B và hàng 3 là B3.) có chứa các số. Các giá trị trống và giá trị văn bản được bỏ qua.
  • Criteria_range1    Bắt buộc. Phạm vi thứ nhất cần đánh giá tiêu chí liên quan trong đó.
  • Criteria1    Bắt buộc. Tiêu chí dưới dạng một số, biểu thức, tham chiếu ô hoặc văn bản xác định những ô nào trong đối số Criteria_range1 sẽ được thêm vào. Ví dụ, các tiêu chí có thể được thể hiện như 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í liên quan của chúng. Cho phép tối đa 127 cặp phạm vi/tiêu chí.

Ghi chú

  • Mỗi ô trong đối số Sum_range sẽ được cộng tổng chỉ khi tất cả các tiêu chí tương ứng đã xác định là đúng cho ô đó. Ví dụ, giả sử một công thức có chứa hai đối số Criteria_range. Nếu ô thứ nhất của Criteria_range1 thỏa mãn Criteria1, và ô thứ nhất của Criteria_range2 thỏa mãn Critera2, thì ô thứ nhất của Sum_range được thêm vào tổng cộng và cứ như vậy đối với các ô còn lại trong các phạm vi đã xác định.
  • Các ô trong đối số Sum_range có chứa TRUE định trị là 1; các ô trong Sum_range có chứa FALSE định trị là 0 (không).
  • Không giống như các đối số tiêu chí và phạm vi trong hàm SUMIF, trong hàm SUMIFS mỗi đối số Criteria_range phải chứa cùng số hàng và cột như là đối số Sum_range.
  • Bạn có thể sử dụng các ký tự đại diện — dấu chấm hỏi (?) và dấu sao(*) — trong tiêu chí. Một dấu chấm hỏi khớp bất kỳ ký tự đơn nào; một dấu sao phù hợp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực, hãy gõ một dấu ngã (~) trước ký tự đó.

Ví dụ

Sao chép dữ liệu của ví dụ trong bảng sau đây và dán vào ô A1 của một trang tính Excel mới. Để công thức hiển thị kết quả, hãy chọn chúng, nhấn F2 và sau đó nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để xem tất cả dữ liệu.

Số lượng Đã bán Sản phẩm Người bán hàng
5 Táo 1
4 Táo 2
15 Atisô 1
3 Atisô 2
22 Chuối 1
12 Chuối 2
10 Cà rốt 1
33 Cà rốt 2
Công thức Mô tả Kết quả
=SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, 1) Cộng tổng số sản phẩm bán được bắt đầu bằng chữ "A" và do Người bán hàng 1 bán. 20
=SUMIFS(A2:A9, B2:B9, "<>Bananas", C2:C9, 1) Cộng tổng số sản phẩm (không bao gồm Chuối) do Người bán hàng 1 bán. 30

Ví dụ 2

Tổng Tài khoản 1 Tài khoản 2 Tài khoản 3 Tài khoản 4
Số tiền tính bằng đô la $100,00 $390,00 8.321,00 $500,00
Lãi suất (2000) 1% 0,5% 3% 4%
Lãi suất (2001) 1% 1,3% 2,1% 2%
Lãi suất (2002) 0,5% 3% 1% 4%
Công thức Mô tả Kết quả
=SUMIFS(B2:E2, B3:E3, ">3%", B4:E4, ">=2%") Tổng số tiền từ mỗi tài khoản ngân hàng có lãi suất lớn hơn 3% cho năm 2000 và lớn hơn hoặc bằng 2% cho năm 2001. Chỉ có Tài khoản 4 đáp ứng cả hai điều kiện. $500,00
=SUMIFS(B2:E2, B5:E5, ">=1%", B5:E5, "<=3%", B4:E4, ">1%") Tổng số tiền từ mỗi tài khoản ngân hàng có lãi suất từ 1% đến 3% cho năm 2002 và lớn hơn 1% cho năm 2001. Tài khoản 2 và Tài khoản 3 đáp ứng cả hai điều kiện. $8.711,00

Ví dụ 3

Số đo Hàng ngày Ngày 1 Ngày 2 Ngày 3 Ngày 4
Mưa (tổng in-xơ) 3,3 0,8 5,5 5,5
Nhiệt độ trung bình (độ) 55 39 39 58
Tốc độ gió trung bình (dặm/giờ) 6,5 19,5 6 6,5
Công thức Mô tả Kết quả
=SUMIFS(B2:E2, B3:E3, ">=40", B4:E4, "<10") Cộng tổng lượng mưa cho các ngày có nhiệt độ trung bình ít nhất là 40 độ Fahrenheit và tốc độ gió trung bình thấp hơn 10 dặm/giờ. 8,8
Lưu ý
Chỉ ô B2 và E2 được cộng vì với mỗi cột (B đến E), giá trị ở cả hàng 3 và 4 phải lần lượt đáp ứng các tiêu chí criteria1 và criteria2. Ô B3 và B4 đáp ứng cả hai tiêu chí, cũng như ô E3 và E4. Tuy nhiên, cả ô C3 và C4 đều không đáp ứng được một trong hai tiêu chí. Cuối cùng, mặc dù ô D4 đáp ứng criteria2, nhưng D3 không đáp ứng criteria1.

Ví dụ 4

Số đo Sáng và Tối Ngày 1 Ngày 2 Ngày 3 Ngày 4
Sáng: mưa (tổng in-xơ) 1,3 0 1,5 3
Chiều: mưa (tổng in-xơ) 2 0,8 4 2,5
Sáng: nhiệt độ trung bình (độ) 56 44 40 38
Chiều: nhiệt độ trung bình (độ) 54 34 38 77
Sáng: tốc độ gió trung bình (dặm/giờ) 13 6 8 1
Chiều: tốc độ gió trung bình (dặm/giờ) 0 33 4 12
Công thức Mô tả Kết quả
=SUMIFS(B2:E3, B4:E5, ">=40", B6:E7, "<10") Cộng tổng lượng mưa cho khoảng thời gian 12 giờ trong đó nhiệt độ trung bình ít nhất là 40 độ Fahrenheit và tốc độ gió trung bình thấp hơn 10 dặm/giờ. 3,5
Chú thích
Chỉ ô B3, C2 và D2 được cộng tổng vì các ô tương ứng của các ô này đáp ứng cả hai tiêu chí. Các ô tương ứng cho B3 là B5 và B7, các ô tương ứng cho C2 là C4 và C6 và các ô tương ứng cho D2 là D4 và D6.
Một ví dụ về dữ liệu bị loại trừ khỏi phép toán, các ô tương ứng của B2 (B4 và B6) không đáp ứng cả hai tiêu chí; cụ thể, ô B6 không đáp ứng vì giá trị của nó (13) lớn hơn criteria2 (10).

Ví dụ 5

Ngôi nhà Foot vuông Phòng ngủ Phòng tắm Nhà để xe Năm xây dựng Giá
Nhà 1 1200 2 1 1940 $125.000
Nhà 2 1580 3 1,5 không 1965 $217.000
Nhà 3 2200 4 3 2003 $376.000
Nhà 4 1750 3 2,5 2001 $249.000
Nhà 5 2140 4 3 1998 $199.000
>2 y*
Công thức Mô tả Kết quả
=SUMIFS(G2:G6,C2:C6,A7,E2:E6,B7,F2:F6,">1999",F2:F6,"<2004") Tính tổng giá của những ngôi nhà có ít nhất 3 phòng ngủ, 1 nhà để xe và có tuổi đời từ 5 đến 10 năm kể từ năm 2009. $625.000
=AVERAGEIFS(G2:G6,C2:C6,">2",E2:E6,C7,F2:F6,">1999",F2:F6,"<2004") Trả về giá trung bình cho những ngôi nhà có ít nhất 3 phòng ngủ, 1 nhà để xe và có tuổi đời từ 5 đến 10 năm kể từ năm 2009. Ngoài "2" và "1999," công thức dùng C7 (y*) làm tiêu chí ký tự đại diện. $312.500

Đầu Trang Đầu Trang

 
 
Áp dụng cho:
Excel 2013, Excel Online