Hàm SUMIF là hàm tính tổng dữ liệu có điều kiện theo một và chỉ một điều kiện duy nhất. Bài viết sau, chúng ta sẽ cùng đi tìm hiểu chức năng chính của hàm SUMIF và các cách vận dụng nó trong công việc hàng ngày nhé!
Nghe nói đến hai hàm SUMIF và hàm VLOOKUP xử lý dữ liệu trong Excel thì không còn xa lạ gì với người chuyên về tin học văn phòng, bởi hai hàm cơ bản này rất được ưu dùng. Tuy nhiên rằng với những bạn mới làm quen với Microsoft Excel thì chắc cũng chưa thể thực sự thành thục cách dùng hàm SUMIF trong Excel, cũng như cách kết hợp hàm SUMIF và VLOOKUP có ích tới mức nào. Vậy hãy cùng khám phá những điều bổ ích này trong nội dung ngay sau đây nhé !
I. Nhắc lại về hàm SUMIF và hàm VLOOKUP
1. Hàm SUMIF
Hàm SUMIF trong Excel là một hàm để tính tổng các ô theo điều kiện đáp ứng một tiêu chí duy nhất. Cách dùng hàm SUMIF có thể được sử dụng để tính tổng các ô dựa trên ngày, số và văn bản tương ứng phù hợp với tiêu chí cụ thể.
Công thức tổng quát của hàm Sumif trong Excel:
= SUMIF (phạm vi cần chọn, tiêu chí chọn, [sum_range])
- Phạm vi ( range) phạm vi của các ô dữ liệu mà bạn muốn áp dụng tiêu chí.
- Tiêu chí ( Criteria ) các tiêu chí cụ thể được sử dụng để xác định các ô cần thêm.
- [sum_range] ( [tùy chọn] ) Các ô để thêm vào với nhau, nếu phần trường [sum_range] bị bỏ qua thì các ô tương ứng trong phạm vi sẽ được thêm vào cùng nhau.
2. Hàm Vlookup
Hàm Vlookup là một hàm cơ bản trong Excel tích hợp được tạo ra để tra cứu và truy xuất dữ liệu từ một cột cụ thể được cho trong bảng. Đối với một giá trị cụ thể được chỉ định, hàm sẽ thực hiện tìm hoặc tra cứu giá trị trong một cột dữ liệu và trả về giá trị dữ liệu tương ứng từ một cột khác.
Công thức tổng quát của hàm Vlookup trong Excel:
= VLOOKUP (giá trị cần xử lý, vị trí bảng, col_index, [phạm vi_lookup])
- Giá trị (value) là phần giá trị cần tìm trong cột đầu tiên của bảng.
- Bảng (Table_array) là đối số chiếu tới bảng để lấy giá trị.
- Col_index là phần cột trong bảng để lấy giá trị .
- Phạm vi_lookup - [tùy chọn] TRUE = khớp gần đúng (được đặt mặc định). SAI = khớp chính xác.
II. Tổng hợp cách kết hợp hàm SUMIF và Vlookup
1. Kết hợp hàm VLOOKUP và SUMIF
Kết hợp hàm VLOOKUP và SUMIF
Trong ví dụ này, chúng ta sẽ sử dụng cách dùng hàm SUMIF và VLOOKUP đi tính tổng sản lượng của Cam trong tháng 1, tháng 2, tháng 3 qua các công thức sau đây:
=SUM(VLOOKUP( A2 , A1:I8 , {2,3,4} , FALSE ))
Sau khi nhập công thức này, các bạn cần hết sức lưu ý vì đây là công thức mảng. Vậy nên sau khi nhập công thức, bạn phải trực tiếp sử dụng tổ hợp phím CTRL + SHIFT + ENTER để có thể nhập được công thức và được trả về kết quả đúng.
Để các bạn có thể hiểu về ví dụ này hơn, chúng ta sẽ phân tích công thức mảng này của hàm SUMIF trong Excel bằng cách đưa ra 3 công thức tương đương sau đây:
=VLOOKUP( A2 , A1:I8 , 2 , FALSE ) + VLOOKUP( A2 , A1:I8 , 3 , FALSE ) + VLOOKUP( A2 , A1:I8 , 4 , FALSE )
Phần số {2,3,4} ở trong công thức mảng phía trên có nghĩa là cột 2,3,4 trong mảng dữ liệu tương ứng A1:I8. Đọc đến đây, có thể các bạn sẽ lập tức có câu hỏi, tại sao chúng ta không sử dụng công thức sau đây để thực hiện tính tổng
=SUM(B2:D2)
Mục đích của việc đưa ra những công thức phức tạp này, chúng ta sẽ phục vụ cho việc tạo báo cáo hoặc có thể là dashboard. Nếu chúng ta sử dụng một công thức tính tổng dữ liệu đơn thuần bằng hàm SUM, khi chúng ta có 1 ô chứa dữ liệu về các loại hàng hoá, muốn thay đổi ô này để thực hiện tính tổng sản lượng của 1 sản phẩm, chúng ta phải thay đổi toàn bộ công thức SUM theo.
Với công thức mảng kết hợp hợp lý giữa hàm SUM và VLOOKUP như ở trên, chúng ta có thể tạo ra 1 báo cáo chính xác về sản lượng của các sản phẩm 1 cách nhanh chóng như sau:
Tạo ra 1 báo cáo chính xác
2. Tổng hợp dữ liệu không thêm cột phụ, kết hợp hàm LOOKUP và hàm SUM
Trong ví dụ dưới đây, chúng ta có 2 bảng dữ liệu, 1 bảng gồm có thông tin sản phẩm và đơn giá; bảng thứ 2 bao gồm dữ liệu về khách hàng, sản phẩm và số lượng sản phẩm khách hàng đã mua. Chúng ta sẽ có nhu cầu đi tính tổng giá trị của 1 khách hàng
Tổng hợp dữ liệu không thêm cột phụ, kết hợp hàm LOOKUP và hàm SUM
Bình thường, chúng ta sẽ cần sử dụng cột phụ như sau:
Chúng ta sẽ cần sử dụng cột phụ
Các công thức cần nhập vào như sau:
G2=VLOOKUP(E2,bang_SP,2,FALSE)
H2=F2*G2
K2=SUMIF(D:D,K1,H:H)
Trong trường hợp không thực hiện thêm được cột phụ, chúng ta có thể sử dụng công thức hàm SUM thay cho hàm SUMIF sau tại K2:
K2=SUM(LOOKUP($E$2:$E$8,$A$2:$A$8,$B$2:$B$8)*$F$2:$F$8*($D$2:$D$8=K1))
Chú ý khi sử dụng công thức kết hợp hàm SUM và hàm VLOOKUP này:
- Cột A trong trang tính phải được sắp xếp theo thứ tự tăng dần hoặc từ A đến Z, để hàm LOOKUP trong Excel cho chúng ta giá trị đúng.
- Công thức chính xác được nhập vào bằng tổ hợp phím CTRL + SHIFT + ENTER. Nếu không thực hiện sử dụng tổ hợp phím này, bạn có thể thay hàm SUM trong Microsoft Excel bằng hàm SUMPRODUCT
Công thức kết hợp này được hiểu như thế nào?
- Phần LOOKUP($E$2:$E$8,$A$2:$A$8,$B$2:$B$8) là trường cho chúng ta kết quả tương ứng như cột đơn giá trong hình chụp phía trên
- Phần $F$2:$F$8 là mảng thông tin số lượng các sản phẩm
- Phần ($D$2:$D$8=K1) khi được nhập sẽ tạo ra 1 mảng gồm các giá trị đúng và sai, khi lấy mảng này đem nhân với 1 số, thì quy tắc cụ thể sau đây được áp dụng: (TRUE được Microsoft Excel định nghĩa là 1, FALSE được Microsoft Excel định nghĩa là 0)
Và cuối cùng, hàm SUM trong Excel sẽ tính tổng và cho ta kết quả cuối cùng
3. Kết hợp cách dùng hàm SUMIF trong Excel và hàm VLOOKUP tra cứu và tổng hợp dữ liệu theo điều kiện
Ví dụ:
Ví dụ
Trong ví dụ này, chúng ta lần lượt có 2 bảng, 1 bảng là thông tin tên Telesale và ID của họ, 1 bảng khác chỉ có dữ liệu về ID và doanh số. Nhiệm vụ của chúng ta ở ví dụ này: cần đi tính tổng doanh số của bất kì Telesale nào dựa vào thông tin về tên của họ.
Công thức cụ thể chúng ta sẽ sử dụng ở đây là
=SUMIF(D:D,VLOOKUP(H1,A1:B8,2,FALSE),E:E)
Hàm VLOOKUP sẽ có nhiệm vụ chính là đưa lại mã ID của Telesale với tên tương ứng, dựa vào phần thông tin ID này chúng ta áp dụng hàm SUMIF như một ví dụ thực tế đơn giản.
4. Tính tổng nhiều điều kiện ở cùng 1 cột bằng hàm SUM và hàm SUMIF
Chúng ta có ví dụ tính tổng tương ứng sau đây, điều kiện tính tổng đều năm ở 1 cột: Dùng hàm SUMIF trong Excel và hàm SUM tính tổng của các giao dịch tương ứng với đầu mã là 111 và 131
Tính tổng nhiều điều kiện ở cùng 1 cột bằng hàm SUM và hàm SUMIF
Để làm được điều này, chúng ta có 3 cách tính bằng hàm SUM và hàm SUMIF trong Excel, với cách mà công thức có dấu {}, nghĩa là ở phần công thức mảng, bạn cần nhấn tổ hợp phím tắt CTRL + SHIFT + ENTER ngay sau khi nhập công thức trong Excel để được kết quả
=SUMIF(A:A,”111*”,C:C)+SUMIF(A:A,”131*”,C:C)
{=SUM(SUMIF(A:A,{“111*”,”131*”},C:C))}
{=SUM(C2:C14*(–(LEFT(A2:A14,3)={“111″,”131”})))}
III. Sử dụng hàm SUMIF có nhiều điều kiện OR
1. Ví dụ 1. SUMIF + SUMIF
Giải pháp được cho là đơn giản nhất chính là tính tổng các kết quả trả về bởi một số hàm SUMIF. Ví dụ: công thức ứng dụng cách dùng hàm SUMIF sau đây sẽ minh họa cách tính tổng số sản phẩm do Mike và John cung cấp:
= SUMIF (C2: C9, “Mike”, D2: D9) + SUMIF (C2: C9, “John”, D2: D9)
Ví dụ 1
Như bạn thấy, hàm SUMIF trong Excel đầu tiên cho biết số lượng tương ứng với “Mike”, còn cách dùng hàm SUMIF kia thì trả lại số tiền liên quan đến “John” và sau đó thì bạn chỉ việc cộng hai kết quả này lại.
2. Ví dụ 2. SUM và SUMIF với đối số mảng
Các giải pháp được nêu trên trên rất đơn giản và thực sự hiệu quả cao khi chỉ có một vài điều kiện. Nhưng một công thức SUMIF + SUMIF có thể được phát triển ra thành rất nhiều ứng dụng khác nếu bạn muốn tính tổng các giá trị với nhiều điều kiện OR. Trong trường hợp này, cách tiếp cận được cho là tốt hơn chính là sử dụng một đối số như điều kiện mảng tương ứng trong hàm SUMIF, như sau:
Bạn có thể bắt đầu tính tổng bằng cách liệt kê tất cả các điều kiện cần có, được ngăn cách bởi dấu phẩy và sau đó chúng ta đặt chúng trong {dấu ngoặc nhọn}, điều này được Microsoft Excel gọi là mảng.
Trong ví dụ được nêu ở trước, nếu bạn muốn thực hiện tính tổng các sản phẩm do John, Mike và Pete cung cấp, điều kiện dữ liệu mảng của bạn sẽ như thế này {“John”, “Mike”, “Pete”}. Và hàm SUMIF trong Excel hoàn chỉnh là: =SUMIF (C2: C9, {“John”, “Mike”, “Pete”}, D2: D9).
Đối số mảng bao gồm 3 giá trị được chọn bắt buộc phải có trong công thức hàm SUMIF để trả về tương ứng ba kết quả độc lập, nhưng vì chúng ta đã viết công thức trong một ô duy nhất, nó sẽ chỉ trả lại kết quả tương ứng chính xác đầu tiên – tức là tổng số sản phẩm do John cung cấp. Để nó hoạt động, bạn phải sử dụng trong đó một vài mẹo – lồng công thức hàm SUMIF của bạn trong một hàm SUM, cụ thể như sau:
= SUM (SUMIF (C2: C9, {“John”, “Mike”, “Pete”}, D2: D9))
Ví dụ 2
Như bạn thấy, một điều kiện mảng cụ thể như trên đã làm cho công thức nhỏ gọn hơn nhiều so với việc sử dụng hàm SUMIF + SUMIF, và chúng có thể cho phép bạn thêm nhiều giá trị như bạn muốn tồn tại trong mảng đó.
Phương pháp hữu ích này sẽ chạy với các con số hoặc thậm chí là các giá trị văn bản. Ví dụ: nếu thay vì bạn có thông tin về tên các nhà cung cấp trong cột C, mà bạn chỉ có phần dữ liệu ID nhà cung cấp như 1, 2, 3, v.v … thì công thức hàm SUMIF kết hợp với SUM của bạn sẽ như sau:
= SUM (SUMIF (C2: C9, {1,2,3}, D2: D9))
Không giống như các giá trị văn bản được cho trong Microsoft Excel, các con số không cần phải được bao gồm phần được cho trong dấu nháy kép trong đối số mảng.
3. Ví dụ 3. hàm SUMPRODUCT và hàm SUMIF
Trong trường hợp, bạn cần phải liệt kê các điều kiện trong vài ô thay vì chỉ định chúng trực tiếp trong công thức, thì lúc này bạn có thể sử dụng hàm SUMIF kết hợp với hàm SUMPRODUCT trong Excel để nhân nhiều thành phần trong các mảng xác định của nó rồi sau đó trả lại tổng số lượng của các sản phẩm đó.
= SUMPRODUCT (SUMIF (C2: C9, G2: G4, D2: D9))
Trong trường hợp ở ô G2: G4 là các ô chứa điều kiện cần có của bạn, thì thông tin về tên nhà cung cấp trong trường hợp này, được minh họa như trong ảnh chụp màn hình của 123job.vn bên dưới.
Nhưng tất nhiên, không có gì ngăn cản nếu bạn muốn thực hiện liệt kê các giá trị trong một điều kiện mảng của hàm SUMIF:
= SUMPRODUCT (SUMIF (C2: C9, {“Mike”, “John”, “Pete”}, D2: D9))
Kết quả trả về ngay sau đó bởi cả hai công thức trên sẽ giống như những gì bạn thấy trong hình:
Kết quả
IV. Một số lưu ý quan trọng khi sử dụng hàm SUMIF trong Excel
1. Cố định vùng tính tổng, vùng điều kiện: Thêm Dấu $ trong Excel để cố định dòng cột của công thức
Tại sao phải thêm dấu $ trong công thức đối với vùng điều kiện tương ứng và vùng tính tổng? - Vì khi làm như vậy ta chỉ cần viết công thức cụ thể cho ô đầu tiên rồi thực hiện copy cho tất cả các ô còn lại. Khi đó bạn sẽ viết được công thức chính xác cho hàng triệu dòng chỉ trong nháy mắt.
2. Tính tổng với điều kiện lớn hơn, nhỏ hơn hoặc bằng một giá trị cho trước chỉ cần thêm dấu >/ =
Tính tổng với điều kiện lớn hơn, nhỏ hơn hoặc bằng
Trong ví dụ trên, nếu muốn tính dữ liệu lương của tất cả những ai có mức lương nhỏ hơn 5.000.000: Bạn chỉ cần nhập vào là
= SUMIF (G5:G14, “
Lưu ý: Nếu bạn chỉ nhập hàm SUMIF thì Microsoft Excel khi đó sẽ không hiểu được bạn muốn gì
Mấu chốt của hàm SUMIF trong trường hợp này chính là điều kiện “nhỏ hơn 5 triệu đồng” viết như thế nào để Microsoft Excel hiểu được
3. Dùng hàm SUMIF để thực hiện tính tổng các giá trị khác với 1 giá trị nào đó khác trong Microsoft Excel được viết như sau:
Dùng hàm SUMIF để thực hiện tính tổng các giá trị
Ví dụ: Tính tổng lương của cả công ty trừ những người có cấp Trưởng phòng. Công thức hàm SUMIF sẽ như sau:
= SUMIF (E5:E14, “”&”TP”,G5:G14) = 48.200.000 đ
V. Kết luận
Hy vọng bài viết trên cùng những chia sẻ vừa rồi sẽ giúp các bạn cải thiện kỹ năng excel, cụ thể là việc sử dụng hàm sumif của mình. Hãy luôn ủng hộ và đón đọc những bài viết về kỹ năng tin học văn phòng của 123job.vn nhé các bạn.
Trang chủ 
Mẫu CV 
Nhắn tin 
Tài khoản 
Thêm