Tính tổng các ô giống nhau trong cột Excel

Tính tổng các mã giống nhau là thao tác mà rất nhiều người gặp phải trong quá trình làm việc. Đặc biệt khi làm thống kê/tổng kết, bạn sẽ gặp trường hợp cần tính tổng (số lượng, thành tiền) các mã sản phẩm giống nhau. Bài viết của Ketoan.vn sẽ chia sẻ với các bạn cách tính tổng các mã giống nhau trong Excel.

Ví dụ ta có bảng dữ liệu như hình dưới. Yêu cầu cần tính tổng số tiền của các mặt hàng là trà.

1. Sử dụng hàm SUMIF

Như các bạn có thể thấy trong hình trên thì có 3 loại trà đó là trà đào, trà chanh và trà quất. Để thực hiện tính tổng tiền của các loại trà thì ta cần sử dụng hàm SUMIF kết hợp với dấu sao (*) là kí tự đại diện cho bất kì kí tự nào theo công thức:

=SUMIF(mảng tham chiếu điều kiện,”*”& “chuỗi kí tự tìm kiếm”&”*”,vùng cần tính tổng)

Áp dụng công thức trên vào bảng tính ta sẽ có công thức tính tổng tiền của các loại mặt hàng trà như sau:

=SUMIF(B2:B10;”*”&”Trà”&”*”;E2:E10)

2. Sử dụng hàm SUMPRODUCT

Một cách để tính tổng số tiền của các mặt hàng là trà đó là ta sử dụng kết hợp hàm SUMPRODUCT với các hàm IFERROR và SEARCH. Các bạn nhập công thức như sau vào Excel rồi nhấn tổ hợp phím Ctrl + Shift + Enter.

=SUMPRODUCT((E2:E10)*(IFERROR(SEARCH(“Trà”;B2:B10);0)>0))

Với công thức trên, hàm IFERROR(SEARCH(“Trà”;B2:B10);0) sẽ tìm kiếm các ô trong cột B có kí tự là “Trà” và trả về giá trị lớn hơn 0, nếu không có sẽ trả về bằng 0. Khi đặt công thức trên vào hàm SUMPRODUCT, Excel sẽ so sánh kết quả đó có >0 hay không, và cộng tổng tiền trong cột E tương ứng với giá trị trên >0.

Mặc dù công thức SUMPRODUCT là công thức dạng mảng, có nghĩa là bạn ko phải áp dụng tổ hợp phím Ctrl + Shift + Enter ở trường hợp thông thường. Tuy hiên với trường hợp này, bắt buộc bạn phải sử dụng tổ hợp phím Ctrl +Shif + Enter nếu không sẽ không ra kết quả chính xác.

Như vậy, bài viết trên đã hướng dẫn các bạn cách tính tổng các mã giống nhau trong Excel. Hy vọng bài viết sẽ hữu ích với các bạn trong quá trình làm việc. Chúc các bạn thành công!

Xem thêm

Mời bạn đọc tải về phần mềm kế toán thuế miễn phí trên Excel

Cách vẽ 2 biểu đồ Excel trên cùng 1 đồ thị: Nhanh và đơn giản

Mời bạn đọc tải về file Excel tờ khai thuế GTGT mẫu số 01/GTGT

Cánh tính số ngày, chênh lệch giữa hai mốc thời gian trong Excel

Cách sử dụng hàm VLOOKUP trả về mảng giá trị trong Excel

Excel đã phát triển công cụ mạnh mẽ và hữu ích để cộng giá trị thỏa mãn một hay nhiều điều kiện là hàm SUMIF và SUMIFS. Nhưng để tìm tổng các giá trị có các ô có một đoạn mã giống nhau thì sẽ sử dụng công thức nào? Đây là câu hỏi rất thú vị mà nhiều bạn đọc gửi cho Thủ Thuật Phần Mềm. Hãy tìm hiểu cách làm trong bài viết sau đây nhé.

Sử dụng hàm SUMIF và cột phụ

Bạn xét ví dụ cụ thể sau:

Bước 1: Sử dụng cột phụ để nhận diện các dòng có kí tự thỏa mãn điều kiện.

(1) Bạn tạo cột phụ với tiêu đề là Áo sơ mi và Nữ.

(2) Trên cột điều kiện Áo sơ mi, cụ thể ô H3, bạn gõ công thức là: H3 =IFERROR(SEARCH("Áo sơ mi",D3),0). Có nghĩa là Excel sẽ tìm trong ô D3 có kí tự nào thỏa mãn Áo sơ mi và trả về vị trí của kí tự đó trong chuỗi. Còn nếu không có kí tự nào thỏa mãn điều kiện thì sẽ trả về vị trí 0. Sao chép công thức cho toàn bộ các ô trong cột bạn thu được kết quả như sau:

(3) Tương tự với cột Nữ, bạn thay điều kiện từ Áo sơ mi => Nữ và thu được kết quả:

Như vậy, những dòng có mã thỏa mãn điều kiện thì cột phụ sẽ có giá trị lớn hơn 0.

Bước 2: Cộng các dòng có giá trị lớn hơn không ở cột phụ bằng hàm SUMIF.

Để cộng Doanh thu theo điều kiện sản phẩm là Áo sơ mi, ở ô K3 bạn gõ công thức: K3 =SUMIF(H3:H12,">0",G3:G12).

Tương tự với ô K4, bạn gõ công thức K4 =SUMIF(I3:I12,">0",G3:G12) để cộng Doanh thu thỏa mãn điều kiện sản phẩm chứa mã kí tự là nữ.

Lưu ý: Với cách làm trên, Excel không phân biệt kí tự là chữ in hoa hay in thường.

Sử dụng hàm SUMIF và ký tự đại diện

Nếu như bạn không muốn sử dụng cách thêm cột phụ, bạn có thể sử dụng kết hợp với dấu sao (*) là kí tự đại diện cho bất kì kí tự nào theo công thức:

=SUMIF(mảng tham chiếu điều kiện,"*"& "chuỗi kí tự tìm kiếm"&"*",vùng cần tính tổng). Bạn thay đổi mục chữ in nghiêng phù hợp với báo cáo của mình.

Với ví dụ tương tự như trên Thủ Thuật gõ công thức: K3 =SUMIF(D3:D12,"*"&"Áo sơ mi"&"*",G3:G12), K4 =SUMIF(D3:D12,"*"&"nữ"&"*",G3:G12).

Và kết quả thu được là:

Lưu ý: Để có thể sao chép công thức, bạn nên sử dụng vị trí tuyệt đối để cố định mảng tham chiếu và vùng cần tính tổng. Bạn thay thế công thức trên bằng
K3=SUMIF($D$3:$D$12,"*"&J3&"*",$G$3:$G$12). Trong đó J3 là ô chứa chuỗi kí tự cần tìm kiếm. Kết quả của ô tính không thay đổi.

Sử dụng hàm SUMPRODUCT

Hàm SUMPRODUCT là một trong những hàm dạng mảng đặc biệt của Excel.

SUMPRODUCT sử dụng phạm vi ô làm đối số của nó cùng nhau các mục trong các mảng, rồi tổng kết quả.

Với ví dụ trên, bạn nhập công thức: K3 =SUMPRODUCT((G3:G12)*(IFERROR(SEARCH("áo sơ mi", D3:D12), 0)>0 )), rồi ấn Ctrl +Shif + Enter.

• Với công thức trên, hàm IFERROR(SEARCH("áo sơ mi", D3:D12), 0) sẽ tìm kiếm các ô trong cột D có kí tự là áo sơ mi và trả về giá trị lớn hơn 0, nếu không có sẽ trả về bằng 0.
• Khi đặt công thức trên vào hàm SUMPRODUCT, Excel sẽ so sánh kết quả đó có >0 hay không, và cộng tổng doanh thu trong cột Doanh thu (Cột G) tương ứng với giá trị trên >0.

Lưu ý: Mặc dù công thức SUMPRODUCT là công thức dạng mảng, có nghĩa là bạn ko phải áp dụng tổ hợp phím Ctrl + Shift + Enter ở trường hợp thông thường. Tuy hiên với trường hợp này, bắt buộc bạn phải sử dụng tổ hợp phím Ctrl +Shif + Enter nếu không sẽ không ra kết quả chính xác.

Với cách làm tương tự với ô K4, Thủ Thuật Phần Mềm tìm ra tổng doanh thu thỏa mãn các mặt hàng có kí tự nữ. Chúc các bạn thành công!

Trong trường hợp phải tính tổng theo điều kiện là những ký tự giống nhau ở trong 1 ô thì dùng hàm gì? Đây là câu hỏi rất thú vị mà Học Excel Online thường nhận được. Hãy cùng tìm hiểu cách làm trong trường hợp này nhé.

Ví dụ chúng ta có yêu cầu như sau:

Trong cột ID nhân viên, chúng ta thấy các mã ID có chứa các ký tự là KD1, KD2. Nhưng vị trí của các ký tự này không giống nhau, tức là nó có thể nằm ngẫu nhiên ở bất kỳ vị trí nào trong ô, miễn là có đúng ký tự đó.

Yêu cầu là tính tổng doanh thu của những nhân viên có mã ID lần lượt là KD1 và KD2.

Cách tính tổng theo các ô có ký tự giống nhau trong Excel

Trong bài toán trên, mới đọc thì thấy khá phức tạp. Nhưng thực ra ta có rất nhiều cách tính tổng trong Excel và bạn sẽ bất ngờ khi biết chúng ta có nhiều cách giải:

Cách 1: Tạo cột phụ nhận biết giá trị điều kiện tính

Bởi vì các ký tự nằm lẫn trong chuỗi ký tự ở mỗi ô nên chúng ta có thể dùng hàm xác định riêng từng ký tự xem ô nào chứa ký tự đó. Chèn thêm cột phụ phía sau cột Doanh thu và dùng hàm SEARCH xác định như sau:

Trong câu lệnh IFERROR(SEARCH($C$1,A2),0) ta có:

• Thứ 1: Search giá trị trong ô C1 (là KD1) xem có trong ô A2 hay không. Kết quả trả về nếu có sẽ là vị trí ký tự bắt đầu xuất hiện trong ô A2. Nếu không sẽ trả về lỗi #VALUE
• Thứ 2: Kết hợp hàm IFERROR để tránh trường hợp lỗi #VALUE khi không tìm thấy bởi hàm SEARCH, lúc đó giá trị lỗi sẽ được thay bằng số 0

Áp dụng tương tự với giá trị ở ô D1 là KD2

Xem thêm: Hướng dẫn cách nhận biết loại lỗi và cách bẫy lỗi trong Excel

Khi đó kết quả >0 tức là có giá trị cần tìm, =0 là không có giá trị cần tìm.

Bây giờ bài toán trở nên khá đơn giản, chúng ta chỉ cần sử dụng hàm SUMIF để tính kết quả như sau:

Kết quả tổng doanh thu của nhân viên có mã ID là KD1 =SUMIF(C2:C10,”>0″,B2:B10)

Tương tự nhân viên có mã KD2 là =SUMIF(D2:D10,”>0″,B2:B10)

Cách 2: Tính trực tiếp không dùng cột phụ với hàm SUMIF

Việc thêm cột phụ khiến chúng ta phải thêm mỗi điều kiện là 1 cột, tại mỗi cột lại sử dụng nhiều công thức. Tại sao không làm trực tiếp để tiết kiệm tài nguyên của Excel nhỉ? Câu trả lời là hoàn toàn có thể làm trực tiếp được.

Cách viết công thức như sau:

Với điều kiện là KD1, chúng ta có:

H3=SUMIF(A2:A10,”*”&F3&”*”,B2:B10)

• Vùng điều kiện là ở cột A, từ A2:A10 là ID nhân viên
• Điều kiện là “*”&F3&”*” có nghĩa là thêm 2 dấu * ở trước và sau giá trị trong ô F3. Vì dấu * là ký tự đặc biệt nối với ô F3 nên cần đặt trong dấu nháy kép, sau đó sử dụng dấu & để nối ký tự.
• Vùng tính tổng là cột Doanh thu, từ B2:B10

Kết quả cũng bằng với cách thứ 1.

Nếu không muốn sử dụng hàm SUMIF, các bạn có thể thay bằng hàm SUMIFS như sau:

H3=SUMIFS(B2:B10,A2:A10,”*”&F3&”*”)

Các nội dung trong hàm này vẫn giống hàm SUMIF, nhưng thứ tự có thay đổi 1 chút khi vùng tính tổng được đưa lên thành phần thứ 1 ở trong hàm.

Xem thêm: Hàm SUMIF / SUMIFS Tính tổng theo điều kiện

Cách 3: Sử dụng hàm SUMPRODUCT

Hẳn bạn đã nghe nói tới hàm SUMPRODUCT có thể thay thế hoàn toàn cho hàm SUMIF, SUMIFS. Trong ví dụ này chúng ta sẽ xem hàm SUMPRODUCT có thể sử dụng được không nhé:

Công thức tại ô I3 sử dụng hàm SUMPRODUCT như sau:

=SUMPRODUCT(($B$2:$B$10)*(IFERROR(SEARCH(F3,$A$2:$A$10),0)>0))

Các bạn có thể thấy phần IFERROR(SEARCH(F3,$A$2:$A$10),0) chính là nội dung trong cột phụ mà chúng ta đã làm ở trên.

Khi đặt trong hàm SUMPRODUCT, chúng ta sẽ so sánh kết quả đó có >0 hay không, và với những giá trị >0 sẽ dùng để tính tổng trong cột Doanh thu.

* Lưu ý:

Cách viết sau đây không ra kết quả:

=SUMPRODUCT(($B$2:$B$10)*($A$2:$A$10=”*”&F3&”*”)

Rất thú vị phải không nào. Như vậy là Học Excel Online đã giới thiệu cho bạn 3 cách làm bài toán này. Bạn có cách nào khác không? Hãy chia sẻ cùng chúng tôi nhé.

Ngoài ra bạn có thể tham khảo thêm một số bài viết cùng chủ đề:

Tại sao nên dùng hàm SUMIFS thay cho hàm SUMPRODUCT tính tổng theo nhiều điều kiện

Hướng dẫn cách dùng hàm SUMPRODUCT để đếm theo nhiều điều kiện

So sánh hàm COUNTIFS với SUMPRODUCT trong việc đếm theo nhiều điều kiện