Xu Hướng 2/2023 # Hàm Sumif / Sumifs Tính Tổng Theo Điều Kiện # Top 7 View | Hoisinhvienqnam.edu.vn

Xu Hướng 2/2023 # Hàm Sumif / Sumifs Tính Tổng Theo Điều Kiện # Top 7 View

Bạn đang xem bài viết Hàm Sumif / Sumifs Tính Tổng Theo Điều Kiện được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.

Chắc hắn các bạn đã gặp phải trường hợp cần phải tính tổng theo 1 hay nhiều điều kiện trong Excel. Để làm được điều này chúng ta sẽ sử dụng hàm SUMIF để tính tổng theo 1 điều kiện và SUMIFS để tính tổng theo nhiều điều kiện.

Hàm SUMIF – cú pháp và cách sử dụng:

Với hàm SUMIF, các bạn sẽ tính được tổng dựa trên 1 điều kiện duy nhất. Cú pháp như sau:

Tính tổng doanh thu trong tháng 1 cho 1 công ty

Tính tổng doanh thu của một nhân viên A

Tính tổng doanh thu của năm 2016 trở về trước

Tính tổng [đại lượng cần tính tổng] nếu [điều kiện]

=SUMIF(A2:A6,2016,B2:B6)

Ở đây, để thuận tiện nhất cho việc sử dụng SUMIF cũng như chuẩn bị cho những ví dụ phức tạp hơn, chúng ta có thể viết số 2016 vào 1 ô trên Excel, ví dụ ô C1 chẳng hạn. Như vậy khi chúng ta thay đổi ô điều kiện C1 thì kết quả của hàm SUMIF cũng sẽ thay đổi theo một cách dễ dàng và chúng ta không cần sửa lại công thức.

=SUMIF(A2:A6,C1,B2:B6)

Hàm SUMIFS – cú pháp và cách sử dụng:

Hàm SUMIFS sẽ giúp các bạn tính tổng dựa trên nhiều hơn một điều kiện. Tất nhiên các bạn cũng có thể dùng hàm này để tính toán nếu các bạn chỉ có 1 điều kiện, như vậy bạn sẽ phải viết nhiều hơn 1 chữ s. Cú pháp như sau

Để hiểu được công thức này, chúng ta sẽ cùng nhau nhìn vào ví dụ minh hoạ sau đây.

Trong ví dụ này, bạn cần tính xem nhân viên A trong năm 2015 có doanh số bán hàng là bao nhiêu

Công thức của chúng ta để giải quyết bài toán này là

=SUMIFS(B2:B6,A2:A6,E1,C2:C6,F1)

Việc lưu điều kiện ra ngoài, không viết vào trực tiếp vào trong công thức sẽ giúp chúng ta có 1 công thức linh hoạt hơn, khi thay đổi điều kiện cũng dễ dàng hơn và tránh được lỗi khi làm việc với những công thức phức tạp.

Nếu bảng dữ liệu của các bạn bên dưới không có dữ liệu nào nữa, chúng ta có thể viết công thức SUMIFS như sau, sẽ gọn hơn rất nhièu:

mời các bạn theo dõi 2 videos sau đây và đừng quên download tài liệu kèm theo bên dưới.

Video nâng cao về 2 hàm SUMIF và SUMIFS sẽ giúp các bạn sử dụng các ký tự dấu sao và hỏi chấm để tính tổng nâng cao:

Ngoài hàm SumIf, SumIfS giúp tính tổng theo nhiều điều kiện, chúng ta còn có thể sử dụng các hàm khác như SUMPRODUCT để làm việc này. Trong thực tế thì yêu cầu tính toán và đặc điểm dữ liệu sẽ quyết định tới việc chúng ta sẽ cần dùng hàm nào, dùng như thế nào. Do đó bạn cần bổ sung thêm cho mình càng nhiều kiến thức về hàm, về tổ chức dữ liệu thì càng thuận lợi khi làm việc.

Tiếp đó là việc cần phải kết hợp các công cụ của Excel để tăng hiệu quả công việc như: định dạng dữ liệu, định dạng theo điều kiện, trích lọc, sắp xếp, báo cáo bằng Pivot Table, vẽ biểu đồ…

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học Excel từ cơ bản đến chuyên gia dành cho người đi làm

Cách Dùng Hàm Sumif – Hàm Tính Tổng Có Điều Kiện

Hàm SUMIF dùng để làm gì

4.9

/

5

(

28

bình chọn

)

Trong bài viết trước chúng tôi đã hướng dẫn các bạn sử dụng hàm SUM để tính tổng. Trong bài này Nguyễn Hùng sẽ hướng dẫn bạn thêm 1 hàm thông dụng trong Excel nữa và nó cũng là hàm mở rộng của hàm SUM đó là hàm SUMIF.

Hàm SUMIF là hàm tính tổng có điều kiện được sử dụng nhiều trong kế toán, nó giúp tính toán chính xác và hiệu quả các công việc của bạn.

Hàm SUMIF dùng để làm gì

Như đã nói ở trên, hàm SUMIF là hàm tính tổng có điều kiện, nó thường dùng trong kế toán để thực hiện các công việc như:

Cú pháp hàm SUMIF

Cú pháp lệnh: =SUMIF(range, criteria, [sum_range])

Giải thích hàm:

=SUMIF(….): Cú pháp lệnh bắt buộc.

Range (bắt buộc): Là vùng chọn có chứa các ô điều kiện. Trong ví dụ dưới là từ ô F5 đến ô F14 (F5:F14).

Criteria (bắt buộc): Là điều kiện, có thể ở dạng số, chữ, ô tham chiếu, biểu thức hoặc hàm xác định sẽ cộng các ô nào. Cách biểu thị điều kiện như sau: 18, “táo”, F5, “<5”, “18” hoặc TODAY().

Sum_range (tùy chọn): Vùng chọn thực tế cần tính tổng mà không giống vùng chọn trong đối số Range. Nếu đối số Sum_range bị bỏ qua, Excel sẽ cộng các ô được xác định trong đối số Range.

Các bước sử dụng hàm SUMIF

Bước 1: Xác định và chọn vùng chứa các ô điều kiện

Bước 2: Nhập vào điều kiện cần thiết.

Bước 3: Chọn vùng chọn thỏa mãn điều kiện của đề bài, công việc yêu cầu.

Ví dụ hướng dẫn cách dùng hàm SUMIF

Cho bảng lương như sau:

Yêu cầu: Tính tổng tiền lương chi trả cho nhân viên có thâm niên làm việc dưới 5 năm.

Công thức tính tổng theo điều kiện cho ô E15 như sau: =SUMIF(F5:F14,”<5″,E5:E14)

Kết quả tính toán thu được như sau:

Qua ví dụ ở trên chắc hẳn các bạn đã nắm rõ được cú pháp cũng như cách sử dụng hàm SUMIF trong Excel rồi. Tiếp theo thì bạn nên tìm hiểu về hàm SUMIFS để biết thêm cách tính tổng nhiều điều kiện.

Cách Dùng Hàm Sumif Để Tính Tổng Có Điều Kiện Trong Excel

Microsoft Excel cho phép bạn dễ dàng thêm các giá trị vào trong bài báo cáo hay phân tích của bạn. Hàm SUM tiêu chuẩn có thể đáp ứng đầy đủ cho bạn trong hầu hết các trường hợp, nhưng nếu như bạn muốn thực hiện tính tổng có điều kiện trong Excel, bạn cần phải sử dụng hàm SUMIF.

Hàm SUMIF là kết hợp giữa hàm SUM và hàm IF trong Excel để cho phép bạn thêm các giá trị trong một phạm vi dữ liệu đã chọn đáp ứng các tiêu chí cụ thể. Phần IF của hàm xác định dữ liệu nào sẽ khớp với tiêu chí đã chỉ định và phần SUM có nhiệm vụ tính tổng các giá trị thỏa với điều kiện đó.

Ví dụ: Có thể bạn sẽ muốn tính tổng doanh thu hằng năm, nhưng chỉ tính các sản phẩm có doanh số bán lớn hơn 250 đơn hàng.

Ghi chú: Các hướng dẫn trong bài viết này áp dụng cho Excel 2019, 2016, 2013, 2010, và Excel Microsoft 365.

Công thức hàm SUMIF trong Excel

Trong Excel, cú pháp của hàm yêu cầu bạn phải có đầy đủ bố cục của hàm và bao gồm tên, dấu ngoặc và đối số của hàm.

Cấu trúc của hàm SUMIF là:

=SUMIF(Range,Criteria,Sum_range)

Các đối số của hàm cho biết nó đang kiểm tra điều kiện nào và phạm vi dữ liệu là ở đâu để tổng hợp thông tin chính xác.

Range (bắt buộc): Là nhóm các ô bạn muốn đánh giá theo các tiêu chí.

Criteria (bắt buộc): Là giá trị mà hàm sẽ so sánh với dữ liệu trong các ô đã chọn trong mục Range ở trên. Nếu nó tìm thấy kết quả khớp, thì nó sẽ tự động cộng dữ liệu tương ứng trong phần Sum_range. Bạn có thể nhập dữ liệu thực tế hoặc tham chiếu ô đến dữ liệu cho đối số này.

Sum_range (tùy chọn): Hàm sẽ cộng dữ liệu trong phạm vi những ô này nếu nó tìm thấy kết quả khớp. Trường hợp nếu như bạn bỏ trống ô này, nó sẽ tính tổng phạm vi những ô trong mục Range đầu tiên.

Hướng dẫn dùng hàm SUMIF để tính tổng có điều kiện trong Excel

Hướng dẫn này sẽ chỉ cho bạn cách tính tổng doanh số hằng năm của những mặt hàng đã bán được hơn 250 đơn đặt hàng.

Bước 2: Nhấp vào tab Formulas.

Bước 3: Nhấp vào biểu tượng Math & Trig trên thanh Menu chính để mở những hàm có thể sử dụng.

Bước 7: Chọn phạm vi các ô từ D3 đến D9 của bảng tính để nhập các tham chiếu ô này được dạng phạm vi cần tìm kiếm.

Bước 8: Bạn chọn dòng Criteria.

Bước 11: Tiếp theo, bạn nhấp vào dòng Sum_range.

Mẹo: Bạn có thử sử dụng tham chiếu ô để làm giá trị Criteria.

Cách Dùng Sumif Trong Excel Với Nhiều Điều Kiện

SUMIF + SUMIF để tính tổng các ô bằng cái này hoặc cái kia

Nếu bạn đang muốn tính tổng các số trong một cột và sau đó tính tổng của các cột đó thì giải pháp rõ ràng nhất là xử lý từng điều kiện riêng lẻ sau đó cộng các kết quả lại với nhau.

Trong bảng bên dưới, giả sử bạn muốn cộng doanh số cho hai sản phẩm khác nhau như Apple và Lemon. Đối với điều kiện này, bạn có thể nhập các mục quan tâm trực tiếp như ” apple” và ” lemon” vào tiêu chí của 2 hàm SUMIF khác nhau:

Hoặc bạn có thể nhập tiêu chí vào các ô riêng biệt và tham chiếu các ô đó:

Trong đó, A2:A10 là danh sách các mục (phạm vi), B2:B10 là các số để tính tổng (sum_range), E1 và E2 là các mục tiêu chí:

Cách hoạt động của công thức này:

Hàm SUMIF đầu tiên cộng doanh số của Apple, hàm SUMIF thứ hai tỉnh tổng doanh số lemon. Công thức này sẽ cộng các tổng phụ với nhau và xuất ra tổng chính.

SUMIF với hằng số mảng – công thức thu gọn với nhiều tiêu chí

Phương pháp SUMIF + SUMIF hoạt động tốt khi có 2 điều kiện. Trong trường hợp nếu bạn muốn tỉnh tổng với 3 điều kiện trở lên, công thức trên sẽ quá lớn và khó đọc. Thay vào đó sử dụng SUMIF với hằng số mảng, công thức nhỏ gọn hơn và vẫn đạt kết quả tương tự:

Hãy nhớ rằng công thức này hoạt động dựa trên các tiêu chí không đồng thời xảy ra (OR) – một ô được tính tổng khi bất kỳ điều kiện đơn lẻ nào được đáp ứng.

Trong trường hợp này, để tính tổng doanh số cho 3 mặt hàng khác nhau, công thức là:

Trong bức hình trên, các điều kiện được mã hóa cứng trong một mảng, có nghĩa bạn phải cập nhật công thức với mọi thay đổi trong điều kiện. Để tránh điều này, bạn có thể nhập điều kiện vào các ô được xác định trước và tạo cho công thức dưới dạng tham chiếu phạm vi (E1:E3 trong ví dụ này).

Trong Excel 365 hỗ trợ mảng động (dynamic array) , nó hoạt động như một công thức thông thường và được hoàn thành bằng phím ENTER. Trong các phiên bản của Excel 2019, Excel 2016, Excel 2013 trở về trước, công thức mảng động được nhập dưới dạng phím tắt CTRL + SHIFT+ENTER.

Hoạt động của công thức này

Một hằng số mảng mà được được gắn vào các điều kiện của SUMIF buộc nó phải trả về nhiều kết quả dưới dạng một mảng. Trong trường hợp này, đó là 3 lượng khác nhau: apple, lemon và orange.

Để có được tổng số, chúng ta sử dụng hàm Sum và hỗ trợ công thức SUMIF.

SUMPRODUCT và SUMIF để tính tổng các ô có nhiều điều kiện OR

Bạn không muốn sử dụng mảng và đang tìm kiếm một công thức bình thường giúp bạn tính tổng với nhiều điều kiện trong các ô khác nhau? Thay vì Sum thì bạn có thể sử dụng hàm SUMPRODUCT xử lý mảng nguyên bản:

Giả sử các điều kiện nằm trong các ô E1, E2 và E3 thì công thức có dạng như sau:

Công thức này hoạt động như thế nào:

Giống với ví dụ trước, hàm SUMIF trả về một mảng số, đại diện cho tổng từng điều kiện riêng lẻ. SUMPRODUCT cộng các số này lại với nhau và xuất ra tổng cuối. Không giống với hàm SUM, hàm SUMPRODUCT được thiết kế để xử lý mảng, vì vậy nó hoạt động như một công thức thông thường mà bạn không cần sử dụng tổ hợp phím ” CTRL + Shift + Enter”.

SUMIF sử dụng nhiều tiêu chí với các ký tự đại diện

Vì hàm SUMIF trong Excel hỗ trợ các ký tự đại diện nên bạn có thể đưa chúng vào nhiều tiêu chí khi bạn cần.

Ví dụ để tính tổng doanh số bán hàng cho tất cả các loại apples và banana, công thức là:

Nếu điều kiện của bạn nhập vào các ô riêng lẻ, bạn có thể nhập ký tự đại diện trực tiếp vào các ô đó và cung cấp tham chiếu phạm vi làm tiêu chí cho công thức SUMPRODUCT SUMIF:

Trong ví dụ này, chúng ta đặt ký tự đại diện (*) trước tên mặt hàng để khớp với bất kỳ chuỗi ký tự nào đứng trước như Green Apples và chuối Goldfinger , Để nhận tổng số bất kỳ vị trí nào trong ô hãy đặt dấu hoa thị ở cả 2 bên, ví dụ “*apple*”.

Cập nhật thông tin chi tiết về Hàm Sumif / Sumifs Tính Tổng Theo Điều Kiện trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!