Top 11 # Cách Sử Dụng Sumif Trong Excel Xem Nhiều Nhất, Mới Nhất 3/2023 # Top Trend

Như tên cho thấy công thức SUMIF và SUMIFS được hình thành bằng cách kết hợp các hàm SUM và IF . Trong tiếng Anh đơn giản điều này ngụ ý rằng các chức năng này có thể thêm các mục hoặc các ô đáp ứng các tiêu chí cụ thể.

Hãy hiểu chi tiết hơn:

Bạn biết hàm SUM có khả năng thêm các mục và chức năng SUMIF mở rộng khả năng của hàm SUM bằng cách cho phép bạn chọn SUM chỉ những mục đáp ứng các tiêu chí cụ thể.

Đồng thời chức năng SUMIFS là một chức năng tương đối mới hơn (được giới thiệu với Excel 2007) mở rộng khả năng của một hàm SUMIF bằng cách cung cấp cho bạn cơ sở để chỉ thêm những mục đáp ứng một bộ tiêu chí.

Chức năng SUMIF trong Excel:

Theo Microsoft Excel SUMIF được định nghĩa như là một chức năng “Thêm các ô được chỉ định bởi một điều kiện hoặc tiêu chuẩn nhất định”.

Cú pháp của hàm SUMIF như sau:

=SUMIF(range, criteria [, sum_range])

Ở đây, ‘range’ dùng để chỉ các ô mà bạn muốn được đánh giá bởi ‘criteria’.

‘criteria’ đề cập đến điều kiện xác định mục nào sẽ được thêm vào. ‘criteria’ có thể là một số, biểu thức hoặc một chuỗi văn bản.

‘sum_range’ là một đối số tùy chọn, nó xác định các ô sẽ được thêm vào. Nếu ‘sum_range’ đối số được bỏ qua sau đó SUMIF xử lý ‘range’ như ‘sum_range’.

Làm thế nào để sử dụng SUMIF trong Excel:

Bây giờ chúng ta hãy thử và hiểu làm thế nào để áp dụng một công thức SUMIF.

Chúng ta hãy thử áp dụng một SUMIF để tìm ra giải pháp.

Vài ví dụ về SUMIF:

Chúng ta hãy xem xét một số ví dụ mà có thể sử dụng Chức năng SUMIF:

Công thức này kết quả vào: 129

Ví dụ 2: Trong cùng bảng như trên, viết công thức để tìm tổng số tiền kiếm được trước ngày “01/01/2012”.

Ví dụ 3: Trong ví dụ này, chúng tôi có bảng biểu diễn của một số trường học tại các sự kiện địa phương. Ở đây, nhiệm vụ của chúng tôi là tìm tổng số giải thưởng do Trường C giành được trong tất cả các sự kiện.

Công thức này kết quả là 24 là D5 + D7 + D9.

Hàm SUMIFS trong Excel:

Trong các ví dụ trên chúng ta đã thấy làm thế nào để sử dụng chức năng SUMIF, bây giờ chúng ta hãy di chuyển đến chức năng SUMIFS. Như tôi đã nói trước rằng SUMIFS là một phần mở rộng của hàm SUMIF, nó có khả năng thêm các mục từ một phạm vi đáp ứng được nhiều điều kiện.

Theo Microsoft Excel SUMIFS có thể được định nghĩa như là một chức năng “Thêm các ô được chỉ định bởi một bộ điều kiện hoặc tiêu chuẩn nhất định”.

Cú pháp của hàm SUMIFS như sau:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Ở đây, ‘sum_range’ xác định các ô cần được thêm vào dựa trên các điều kiện nhất định. Đó là một trường bắt buộc.

‘criteria_range1’ xác định phạm vi đầu tiên mà các tiêu chí đầu tiên sẽ được đánh giá.

‘ criteria1’ chỉ rõ điều kiện cần được đánh giá trong ‘ criteria_range1’.

‘ criteria_range2’, ‘criteria2’ chỉ định các phạm vi khác và các điều kiện tương ứng. Đó là một đối số tùy chọn. SUMIFS hỗ trợ tổng cộng 127 tiêu chuẩn phạm vi.

Làm thế nào để sử dụng SUMIFS trong Excel:

Để hiểu cách sử dụng hàm SUMIFS, hãy lấy ví dụ:

Vì vậy, chúng tôi sẽ cố gắng áp dụng các chức năng SUMIFS như:

Vài ví dụ về các mục tiêu:

Ví dụ 1: Trong ví dụ này chúng ta lại có một bảng bán hàng, và nhiệm vụ là tìm tổng số đơn vị bán hàng của người bán hàng “Steve” vào năm 2012.

Công thức này cho kết quả 907.

Ví dụ 2: Trong ví dụ này, chúng tôi sẽ cố gắng tìm tổng số tiền được tăng lên từ khu vực “West”, nơi bán các mặt hàng lớn hơn 500.

Điều này cho 38 như là kết quả mà là một khoản B4 và D4.

Vài điểm quan trọng về SUMIF và SUMIFS Công thức:

Cả hai công thức SUMIF và SUMIFS đều hỗ trợ các ký tự đại diện. Bạn có thể sử dụng ký tự đại diện (như: ‘*’ và ‘?’) Trong “criteria”.

Trong SUMIF các ô trong ‘ range’ đối số và ‘sum_range’ không nhất thiết phải có cùng kích thước và hình dạng. Nhưng điều này không đúng trong trường hợp của SUMIFS.

Hàm sumif là hàm tính tổng cho một dãy số hoặc một dãy các ô số (Range) căn cứ vào một điều kiện (Criteria) nào đó.

Ta có công thức tổng quát sau:=SUMIF( Range, Criteria,[sum_range])

Trước tiên ta phân tích tham số Sum_Range. Tham số này đặt trong ngoặc vuông nên nó là tham số tùy chọn.

Khi Sum_Range bị bỏ qua thì hàm sẽ tính tổng cho vùng Range.

Nhưng khi bạn thêm tham số này vào thì Hàm sẽ tính tổng cho Sum_RangeVí du:=Sumif(A2:A7,”apple”,B2:B7) thì hàm sẽ tính tổng cho vùng B2:B7.=Sumif(A2:A7,”apple”) thì hàm sẽ tính tổng cho vùng A2:A7.

Range:– Là vùng chứa giá trị để tính tổng nếu tham số Sum_range được bỏ qua. Các ô chứa Text hoặc rỗng sẽ bị hàm bỏ qua.– Khi có tham số Sum_Range thì Range sẽ là vùng chứa giá trị để đánh giá cho Criteria.

Criteria: Các điều kiện thiết lập cho Hàm SumIF cũng tương tự như Hàm CountIF hoặc Hàm CountIFS ở bài học trước.

Lưu ý: Các điều kiện phải dựa vào giá trị của các ô chứa trong Range. Nếu Range chứa giá trị là Text thì điều kiện Criteria phải ở dạng Text nếu không nó sẽ không tìm thấy điều kiện nào khớp với với điều kiện được thiết lập và trả về giá trị là 0.

Ở ví dụ sau ta có 2 bảng nằm trong 1 sheet như sau:

Ô D2 và D3: Kết quả bằng 0, vì Criteria là lớn hơn giá trị ô B2 (lớn hơn 10) Tuy nhiên Range có giá trị trong dãy A2: A11 là dạng Text (Chữ) nên hàm bỏ qua.

Ô D4 và D5: Kết quả bằng 135, Tính tổng giá trị trong dãy (range) B2:B11 với Criteria là lớn hơn giá trị ô B2 (lớn hơn 10), và range B2: B11 là dạng số (Numberic), nên hàm sẽ tính tổng các ô B3:B7 vì các ô này có giá trị lớn hơn 10.

Ô D7: Khi có tham số Sum_Range (A15:A24) thì hàm sẽ tính tổng các ô nằm trong dãy A15:A24 (Sum_Range) với tiêu chí là giá trị ô A15 (Apple), Lúc này tuy chữ Apple là điều kiện thỏa giá trị nằm trong Range nhưng Sum_Range không chứa giá số nên kết quả vẫn là bằng 0.

Trong thực tế ta thường dùng hàm Sumif để tính tổng các giá trị trên 1 bảng dựa trên tiêu chí xuất hiện trên một bảng.

Vi dụ: Ta có bảng kết quả bán hàng của cửa hàng hoa quả theo từng ngày, cứ mỗi một lượt khách mua một mặt hàng thì sẽ ghi chép vào 1 dòng theo thứ tự từ trên xuống dưới, và lúc này các mặt hàng thường xuyên lặp đi lặp lại. Bây giờ ta tạo một bảng thứ 2 có liệt kê tất cả các loại hoa quả mà cửa hàng đang bán (4 loại bên dưới). Ta dùng hàm Sumif để tính tổng số tiền bán được trên một loại hoa quả như sau:

1. Công dụng của hàm SUMIF trong Excel kế toán:

2. Tác dụng hàm SUmif: Hàm sumif là hàm tính tổng theo điều kiện:

– Dãy ô điều kiện: Là dãy ô chưa điều kiện cần tính. Cụ thể trong bài: Là dãy ô chứa tài khoản trong cột TK Nợ/TK Có trên NKC. Hoặc dãy ô chứa mã hàng trên Phiếu nhập kho, Xuất kho …vv

– Điều kiện cần tính: Phải có ” Tên ” trong dãy ô điều kiện. CỤ thể trong bài : Là Tài khoản cần tính trên NKC hoặc mã hàng trên kho ( bảng Nhập Xuất Tồn ) hoặc TK cần tổng hợp trên bảng Cân đối phát sinh…. ( Điều kiện cần tính chỉ là một ô )

– Dãy ô tính tổng: Là dãy ô chưa giá trị trong cột phát sinh Npj hoặc phát sinh. Có trên NKC, hoặc dãy ô chứa giá trị trong cột số lượng hoặc thành tiền trên kho ( trên phiếu NK, XK ). Dãy ô tính tổng và dãy ô điều kiện phải tương ứng nhau, tức điểm đầu và điểm cuối phải tương ứng nhau.

– Hướng dẫn khi muốn tuyệt đối dòng hoặc cột ( việc tuyệt đối dòng hoặc cột là tuỳ vào từng trường hợp ):

Để có gái trị tuyệt đối ( tuyệt đối được hiểu là cố định cột và cố định dòng – ( $cột$dòng). Ví dụ: $E$12 – tức là cố định cột E và cố định dòng 12

Để có giá trị tương đối cột và tuyệt đối dòng – được hiểu là cố định dòng thay đổi cột – (cột$dòng). Ví dụ: E$12 – tức là cố định Dòng 12, không cố định cột E

Để có giá trị tương đối dòng và tuyệt đối cột – được hiểu là cố định cột thay đổi dòng – ($cột dòng). Ví dụ: $E12 – tức là cố định Cột E, không cố định dòng 12.

Theo công thức trong bảng tính thì:

– Dãy ô điều kiện là E15 đến E180 ( tức là kéo từ nghiệp vụ phát sinh đầu tiên của tháng )– Điều kiện cần tính là ô E112 ( tức là ô điều kiện cần tính )– Dãy ô tính tổng là H15 đến H180

Với các bút toán kết chuyển cuối kỳ thì điều kiện cần tính có thể bấm trực tiếp vào ô chứa nó. Hoặc gõ trực tiếp tài khoản cần kết chuyển vào công thức

Ví dụ: =SUMIF( $E15:$E180, 5111,$H$15:$H$180)

Để các bạn có thể hiểu rõ hơn và chi tiết hơn khi sử dụng hàm SUMIF

Bài viết giúp ta biết Cách sử dụng hàm SUMIF trong Excel kế toán. Xem tại:

https://ketoan68.net/cach-s-dng-ham-sumif-trong-excel-k-toan/(mở trong cửa sổ mới)

Trong bài hướng dẫn này, bạn sẽ tìm thấy một vài ví dụ công thức nâng cao diễn giải cách kết hợp hàm VLOOKUP với hàm SUM hoặc hàm SUMIF trong Excel để tìm và tính tổng các giá trị dựa trên một hay vài tiêu chí.

Nhiệm vụ có thể khác nhau, nhưng có cùng bản chất – bạn muốn tìm và tính tổng các giá trị dựa trên một hay nhiều tiêu chí trong Excel. Đó là loại giá trị gì? Bất cứ giá trị số nào. Vậy tiêu chí là gì? Bất kỳ tiêu chí nào 🙂 Bắt đầu từ một chữ sô hay một tham chiếu đến một ô chứa giá trị chính xác, rồi kết thúc bằng toán tử logic và công thức trong Excel trả về kết quả.

Hãy lưu ý rằng, đây là các ví dụ nâng cao – điều này có nghĩa là bạn đã quen thuộc với các quy tắc và cú pháp cơ bản của hàm VLOOKUP. Nếu không, bạn chắc chắn phải xem qua phần đầu tiên của bài hướng dẫn về hàm VLOOKUP cho người mới bắt đầu.

HÀM VLOOKUP VÀ HÀM SUM TRONG EXCEL – TÍNH TỔNG CỦA CÁC GIÁ TRỊ TRÙNG KHỚP

Giả sử, bạn có danh sách các sản phẩm có đính doanh số trong vài tháng, một cột là một tháng.

DỮ LIỆU NGUỒN – DOANH SỐ HÀNG THÁNG

Bây giờ, bạn muốn lập bảng tính tổng về tổng doanh số của mỗi sản phẩm.

Giải pháp là sử dụng mảng trong thông số thứ ba (col_index_num) của hàm VLOOKUP trong Excel. Đây là công thức hàm VLOOKUP mẫu:

=SUM(VLOOKUP(lookup_value, lookup_range, {2,3,4}, FALSE))

Như bạn có thể thấy, chúng ta sử dụng mảng {2,3,4} trong câu lệnh thứ ba để hiển thị vài giá trị cần tìm trong cùng công thức VLOOKUP để tính tổng các giá trị ở cột 2, 3, và 4.

Và bây giờ, hãy điều chỉnh sự kết hợp của hàm VLOOKUP và hàm SUM đối với dữ liệu của chúng ta để tính tổng tất cả doanh số từ cột B đến cột M trong bảng trên:

=SUM(VLOOKUP(B2, ‘Doanh số hàng tháng’!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13},FALSE))

Quan trọng! Vì bạn đang tạo một công thức mảng, hãy chắc rằng bạn nhấp Ctrl+Shift+Enter thay vì chỉ nhấn mỗi Enter khi bạn gõ xong. Khi bạn thực hiện xong, Microsoft Excel sẽ đóng khung công thức của bạn trong dấu ngoặc nhọn như thế này:

{=SUM(VLOOKUP(B2,’Doanh số hàng tháng’!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13},FALSE))}

Nếu bạn nhấp Enter, thì chỉ có giá tị đầu tiên trong mảng được xử lý, điều này sẽ cho kết quả sai.

Như bạn có thể thấy, việc sử dụng hàm VLOOKUP cùng với hàm SUM rất dễ. Tuy nhiên, đây không phải là giải pháp hoàn hảo, đặc biệt khi bạn đang thao tác với bảng lớn. Điểm mấu chốt ở đây là việc sử dụng công thức mảng có thể gây ảnh hưởng ngược lại hoạt động của sổ làm việc vì mỗi giá trị trong mảng cần một hàm VLOOKUP riêng biệt. Vì thế, càng có nhiều giá trị trong mảng thì càng có nhiều công thức mảng trong sổ làm việc, Excel sẽ chạy càng chậm.

Bạn có thể loại bỏ vấn đề này bằng cách sử dụng hàm INDEX và hàm MATCH thay vì hàm VLOOKUP và hàm SUM, và tôi sẽ cung cấp cho bạn vài ví dụ công thức trong bài viết tiếp theo.

CÁCH THỰC HIỆN CÁC PHÉP TÍNH KHÁC VỚI HÀM VLOOKUP TRONG EXCEL

Nếu ta thêm các công thức trên vào bảng “Tổng doanh số” của ví dụ trước, thì kết quả sẽ tương tự như thế này: Lưu ý. Vì các công thức trên đều là công thức mảng, nên hãy nhớ nhấn Ctrl+Shift+Enter để nhập chúng vào ô được chính xác.

HÀM LOOKUP VÀ HÀM SUM – TÌM KIẾM TRONG MẢNG VÀ TÍNH TỔNG CÁC GIÁ TRỊ TRÙNG KHỚP

Trong trường hợp thông số cần tìm là một mảng thay vì một giá trị riêng lẻ, thì hàm VLOOKUP sẽ không có tác dụng bởi vì nó không thể tìm kiếm trong các mảng dữ liệu. Trong trường hợp này, bạn có thể sử dụng hàm LOOKUP – hàm tương tự với hàm VLOOKUP nhưng có thao tác với mảng và với các giá trị riêng lẻ.

Hãy xem xét các ví dụ sau, để bạn hiểu hơn điều mà tôi nói. Giả sử, bạn có một bảng liệt kê tên khách hàng, sản phẩm mua, và số lượng (Bảng chính). Bạn cũng có bảng thứ hai liệt kê giá sản phẩm (Bảng cần tìm). Nhiệm vụ của bạn là lập công thức tính tổng các đơn hàng đặt bởi các khách hàng xác định.

Có thể bạn vẫn nhớ, bạn không thể sử dụng hàm VLOOKUP trong Excel bởi vì giá trị cần tìm (mảng dữ liệu) xuất hiện rất nhiều lần. Thay vào đó, bạn kết hợp hàm LOOKUP với hàm SUM như thế này:

=SUM(LOOKUP($C$2:$C$10,’Bảng cần tìm’!$A$2:$A$16,’Bảng cần tìm’!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))

Bởi vì đây là công thức mảng, hãy nhớ nhấn Ctrl+Shift+Enter để hoàn thành.

Và bây giờ, hãy phân tích các phần của công thức để bạn hiểu mỗi hàm hoạt động ra sao và có thể chỉnh sửa nó cho dữ liệu của riêng bạn.

Chúng ta sẽ đặt hàm SUM qua một bên, bởi vì công dụng của nó quá rõ ràng, và chỉ tập trung vào 3 thành phần được được nhân với nhau:

LOOKUP($C$2:$C$10,’Bảng cần tìm’!$A$2:$A$16,’Bảng cần tìm’!$B$2:$B$16)

Hàm LOOKUP này tìm kiếm hàng hóa được liệt kê trong cột C trong bảng chính, và trả về giá tiền tương ứng từ cột B trong bảng cần tìm.

Thông số này trả về số lượng mỗi sản phẩm mua bởi khách hàng, số lượng được liệt kê ở cột D trong bảng chính. Khi được nhân với giá tiền, điều này được trả về bởi hàm VLOOKUP ở trên, hàm này sẽ cung cấp cho bạn giá tiền của mỗi sản phẩm đã mua.

Công thức này so sánh tên khách hàng ở cột B với tên khách hàng ở ô G1. Nếu trùng khớp, nó sẽ trả về “1”, nếu không thì nó sẽ trả về “0”. Bạn sử dụng nó chỉ để xóa tên khách hàng không xuất hiện ở ô G1, bởi vì tất cả chúng ta đều biết bất cứ số nào nhân với 0 đều bằng 0.

Bởi vì công thức của chúng ta là công thức mảng nên nó lặp lại quá trình trên cho mỗi giá trị trong mảng cần tìm. Và cuối cùng, hàm SUM tính tổng các kết quả của phép nhân. Chẳng có gì khó khăn cả, đúng không nào?

Lưu ý. Để công thức LOOKUP hoạt động chính xác thì bạn cần phải lọc cột cần tìm trong bảng cần tìm theo thứ tự tăng dần (từ A đến Z). Nếu việc lọc không được chấp nhận đối với dữ liệu của bạn, thì hãy thử qua công thức SUM/TRANSPOSE được gợi ý bởi Leo.

HÀM VLOOKUP VÀ HÀM SUMIF – TÌM KIẾM VÀ TÍNH TỔNG CÁC GIÁ TRỊ ĐÁP ỨNG CÁC TIÊU CHUẨN NHẤT ĐỊNH

Bảng chính chứa nhiều mục có cùng số chứng minh nhân dân theo thứ tự ngẫu nhiên.

Bạn không thể thêm cột “Tên người bán” vào bảng chính.

Và bây giờ, hãy lập công thức mà, đầu tiên, tìm kiếm tất cả doanh số của một người xác định, và thứ hai, tính tổng các giá trị.

Trước khi lập công thức, hãy để tôi nhắc bạn cú pháp của hàm SUMIF:

SUMIF(range, criteria, [sum_range])

range – thông số này đã tự giải thích cho chính nó rồi, chỉ là một dải ô mà bạn muốn đánh giá thông qua các tiêu chuẩn nhất định.

criteria – điều kiện nói cho công thức biết cần tính tổng giá trị nào. Nó có thể được dùng dưới dạng số, tham chiếu ô, biểu thức, hay hàm Excel khác.

sum_range – thông số này là tùy chọn, nhưng rất quan trọng đối với chúng ta. Nó xác định dải ô nơi mà các giá trị của ô tương ứng sẽ được cộng. Nếu loại bỏ, Excel sẽ tính tổng tất cả giá trị của các ô đã được xác định ở câu lệnh thứ nhất (thông số đầu tiên).

Hãy nhớ những điều trên, giờ chúng ta hãy xác định ba thông số cho hàm SUMIF của mình. Có thể bạn vẫn còn nhớ, chúng ta muốn tính tổng tất cả doanh số của một người xác định. Tên của người đó được nhập vào ô F2 trong bảng chính (hãy xem lại hình ảnh ở trên).

Range – bởi vì chúng ta đang tìm kiếm thông qua số chứng minh nhân dân của người bán, nên thông số range cho hàm SUMIF của chúng ta chính là cột B ở bảng chính. Bây giờ, bạn có thể nhập dải ô B:B, hay nếu bạn đổi dữ liệu của mình thành bảng, bạn có thể sử dụng tên cột để thay thế: Main_table[ID]

Criteria – bởi vì chúng ta có tên người bán hàng ở bảng khác (bảng khác), nên chúng ta phải công thức VLOOKUP để tìm số chứng minh nhân dân tương ứng của người đó. Tên của người đó được nhập ở ô F2 trong bảng chính, vì thế chúng ta sử dụng công thức này để tìm kiếm nó:

VLOOKUP($F$2,Lookup_table,2,FALSE)

Tất nhiên, bạn có thể nhập tên trong tiêu chuẩn cần tìm của hàm VLOOUP, nhưng sử dụng tham chiếu ô tuyệt đối thì tốt hơn bởi vì nó tạo ra công thức chung có hiệu quả cho bất cứ tên nào được nhập vào ô xác định.

Sum range – đây là phần dễ nhất. Bởi vì các con số thể hiện doanh thu của chúng ta nằm ở cột C có tên “Doanh số”, chúng ta chỉ cần đặt Main_table[Sales].

Bây giờ, tất cả những gì bạn cần là tập hợp tất cả các phần của công thức lại và công thức SUMIF + VLOOKUP của bạn đã sẵn sàng:

=SUMIF(Main_table[ID], VLOOKUP($F$2, Lookup_table, 2, FALSE), Main_table[Sales])