SUMIF - Tính tổng có một điều kiện

1. Để làm gì?

• Tính tổng các số trong một phạm vi thỏa mãn một yêu cầu nào đó.
• Dữ liệu vật tư nhập xuất tại công trường hằng tháng lên đến hàng trăm thậm chí hàng nghìn đầu vật tư. Vậy làm thế nào bạn có thể tính toán được tổng khối lượng một loại vật tư bất kỳ trong list danh mục vật tư mà kho cung cấp. Cách đơn giản nhất đó là dùng hàm Sumif.
• Tính tổng khối lượng, giá trị công việc nào đó thảo mãn điều kiện lớn hơn, nhỏ hơn, bằng hoặc khác giá trị cần so sánh.

2. Dùng như thế nào?

2.1. Ví dụ minh họa

Đề bài:

• Dữ liệu: Bảng tổng hợp đơn giá cấp phối vữa bê tông.
• Yêu cầu: Tính giá trị vật tư Cát trong các hạng mục công việc khác nhau.

 

Hướng dẫn:

Phân tích yêu cầu

• Trong bảng tổng hợp các hạng mục công việc chứa nhiều loại vật liệu khác nhau.
• Yêu cầu ở đây là chỉ tính Giá trị của vật tư Cát.
• Giá trị vật tư Cát được thể hiện trong các ô H9, H14 và H19.

Giải quyết yêu cầu

• Việc tính giá trị vật tư Cát là phép tính tổng của ba giá trị = H9+H14+H19. Để giải quyết bài toán này thông thường chúng ta sẽ dùng phép tính cộng hoặc hàm Sum tính tổng thông thường.
• Ở đây ta sẽ không làm thủ công như vậy mà sẽ dùng Sumif với điều kiện kiểm tra loại vật tư “Cát”, nếu điều kiện được thỏa mãn thì Sumif sẽ cộng các giá trị đó với nhau.
• Tại ô J7 gõ công thức =SUMIF(D7:D22;“Cát”;H7:H22)

 

Được kết quả như sau:

 

2.2. Cấu trúc

 

 

Có một ví dụ thú vị thế này: Bạn là kỹ sư xây dựng, lương không đủ sống, vợ bạn là một cô gái năng động, ở nhà có buôn bán hoa quả từ miền Tây ra Hà Nội. Vợ bạn kinh doanh được gần một năm rồi. Đơn hàng theo tháng vợ bạn nhập hết thành một bảng danh sách Excel rất dài có rất nhiều loại hoa quả như: Táo, Nho, Sầu riêng, Bưởi, Cam, Na, Mít. Giờ vợ bạn có vài yêu cầu như:

• Anh tính cho em tổng khối lượng từng loại hoa quả năm vừa rồi nhập bao nhiêu? Xem loại hoa quả nào được tiêu thụ nhiều nhất?
• Anh tính cho em tổng giá trị loại quả có giá nhỏ hơn 30 000 đồng/kg?
• Anh tính cho em… đủ các thứ tiêu chí. Vậy bạn phải dùng hàm này để phục vụ vợ bạn nhé!

2.3. Khái niệm 

• Ở ví dụ trên với việc sử dụng hàm Sumif ta có thể tính tổng giá trị vật tư Cát trong cả mảng dữ liệu gồm nhiều loại vật tư khác nhau. 
• Vậy hàm Sumif là hàm dùng để tính tổng các giá trị trong một mảng dữ liệu xác định thỏa mãn một điều kiện xác định nào đó.

Điều kiện cho việc kiểm tra để tính tổng có thể là ngày tháng, văn bản, con số, các phép tính logic cho điều kiện (<, >, <>, =) hay là ký hiệu (*, ?)

3. Cần lưu ý gì không

• Độ lớn của Vùng điều kiện và Vùng tính tổng cách tốt nhất là nên bằng nhau.
• Toán tử và Điều kiện cần kiểm tra trong hàm Sumif cần lưu ý một vài điểm sau để hàm không phát sinh lỗi:

 

• Toán tử và Điều kiện kiểm trả phải nằm trong dấu ngoặc kép: "<>Cát" – Loại trừ vật tư Cát khi tính tổng.
• Toán tử và Điều kiện kiểm tra được liên kết bởi ký hiệu và:
  • "<>" và "Cát" – Loại trừ vật tư Cát khi tính tổng
  • "<" và 10 – Kiểm tra Điều kiện nhỏ hơn 10 để tính tổng

4. Ví dụ áp dụng

4.1. Ví dụ 1: Hàm Sumif với điều kiện văn bản

Đề bài:

• Dữ liệu: Bảng thống kê khối lượng vật tư nhập về công trường theo yêu cầu.
• Yêu cầu:

TH1: Tính giá trị vật tư Xi măng đã nhập về.

TH2: Tính tổng giá trị vật tư nhập về trừ vật tư Cát (Do số liệu Cát đang có vấn đề nên tạm thời bỏ ra chưa tính tổng giá trị).

 

Hướng dẫn:

TH1: Tính giá trị vật tư Xi măng đã nhập về

Phân tích yêu cầu

• Chỉ tính tổng Giá trị Xi măng.
• Giá trị Xi măng được thể hiện tại ô H9 và H12.

Giải quyết yêu cầu

• Điều kiện kiểm tra là Xi măng – nằm trong vùng D7:D12 – Vùng điều kiện.
• Giá trị Vật tư nằm trong vùng H7:H12 – Vùng tính tổng.
• Sumif sẽ tiến hành kiểm tra trong Vùng điều kiện trên nếu Đúng thì Cộng các giá trị đó với nhau trong Vùng tính tổng, ngược lại nếu Sai thì tự động Bỏ qua giá trị đó.
• Vậy, tại ô J7 gõ công thức =SUMIF(D7:D12;"Xi măng";H7:H12)

 

Trong đó:

 

Ta được kết quả sau:

 

TH2: Tính tổng giá trị vật tư nhập về trừ vật tư Cát (Do số liệu Cát đang có vấn đề nên tạm thời bỏ ra chưa tính tổng giá trị).

Phân tích yêu cầu

• Tính tổng giá trị tất cả các vật tư (Trừ vật tư Cát).
• Giá trị các vật tư đó nằm trong Vùng tính tổng. 

Giải quyết yêu cầu

• Điều kiện kiểm tra vật tư khác Cát, nằm trong vùng D7:D12 – Vùng điều kiện.
• Giá trị vật tư nằm trong vùng H7:H12 – Vùng tính tổng.
• Sumif sẽ tiến hành kiểm tra trong Vùng điều kiện trên nếu Đúng thì Cộng các giá trị đó với nhau trong Vùng tính tổng, ngược lại nếu Sai thì tự động Bỏ qua giá trị đó.
• Tại ô J7 gõ công thức =SUMIF(D7:D12;"<>"và"Cát";H7:H12)

 

Trong đó:

 

Kết quả:

 

4.2. Ví dụ 2: Hàm Sumif với điều kiện so sánh số học

Đề bài:

• Dữ liệu: Bảng thống kê khối lượng vật tư nhập về công trường.
• Yêu cầu: Tính tổng giá trị vật tư mà có đơn giá nhỏ hơn đơn giá của Cát = 200789.

 

Hướng dẫn:

Phân tích yêu cầu

• Tính giá trị vật tư nằm trong vùng H7:H12 thỏa mãn điều kiện.
• Điều kiện kiểm tra đơn giá thấp hơn đơn giá của Cát: <200789 (đồng).

Giải quyết yêu cầu

• Tương tự như trên ta dễ dàng xác định được Vùng điều kiện, Điều kiện và Vùng tính tổng.
• Tại ô J7 gõ công thức =SUMIF(G7:G12;"<200789";H7:H12)

 

Trong đó: 

 

Kết quả: Đơn giá vật tư nhỏ hơn đơn giá của Cát = 200789 chỉ có đơn giá của Xi măng.

 

4.3. Ví dụ 3: Hàm Sumif với điều kiện ngày tháng

Đề bài:

• Dữ liệu: Bảng thống kế khối lượng vật tư nhập về công trường.
• Yêu cầu: Tính tổng giá trị vật tư nhập về trước ngày 29/03/2018.

 

Hướng dẫn:

Phân tích yêu cầu

• Điều kiện kiểm tra dạng ngày tháng: trước ngày 29/03/2018.

Giải quyết yêu cầu

• Với điều kiện để kiểm tra tính Tổng giá trị là ngày tháng cũng giống như việc kết hợp các toán tử so sánh với số học.
• Tại ô J7 triển khai công thức =SUMIF(C7:C12;"<29/3/2018";H7:H12)

 

Trong đó:

 

Kết quả:

 

---

---