Hướng dẫn các cách tính tổng các mã giống nhau trong Excel có ví dụ minh họa cụ thể

Excel là một trong những phần mềm vô cùng 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. Tuy nhiên, trong trường hợp nếu như chúng ta cần tìm tổng các giá trị có các ô có một đoạn mã giống nhau thì sẽ thực hiện như thế nào và cần sử dụng công thức gì?

Trong bài viết sau đây, studyexcel sẽ chia sẻ về các cách tính tổng các mã giống nhau trong Excel một cách chi tiết, cụ thể và kèm theo ví dụ minh họa để các bạn có thể dễ dàng thực hiện thao tác hơn.

1. Cách tính tổng các mã giống nhau trong Excel bằng cách tạo cột phụ và hàm SUMIF

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

cách tính tổng các mã giống nhau trong Excel
cách tính tổng các mã giống nhau trong Excel

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 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:

cách tính tổng các mã giống nhau trong Excel
cách tính tổng các mã giống nhau trong Excel

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

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:

cách tính tổng các mã giống nhau trong Excel
cách tính tổng các mã giống nhau trong Excel

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)

2. Cách tính tổng các mã giống nhau trong excel trực tiếp bằng 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:

cách tính tổng các mã giống nhau trong Excel
cách tính tổng các mã giống nhau trong Excel

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.

3. Cách tính tổng các mã giống nhau trong excel bằ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ách tính tổng các mã giống nhau trong Excel
cách tính tổng các mã giống nhau trong Excel

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&”*”)

Qua bài viết chia sẻ của mình về các cách tính tổng các mã giống nhau trong Excel, studyexcel hy vọng các bạn có thể nắm rõ và có thể ứng dụng để xử lý công việc của mình. Tuy nhiên mỗi cách tính tổng các mã giống nhau trong Excel trên sẽ có những ưu điểm và hạn chế riêng nên tùy vào yêu cầu từng bài để có thể áp dụng nhé.

Related Posts

[HƯỚNG DẪN] Cách Gửi Folder Qua Gmail Chi Tiết Và Nhanh Chóng

Bạn có bao giờ muốn gửi một folder chứa nhiều file qua Gmail cho người khác mà không biết cách làm thế nào? Bạn có thể nghĩ…

[HƯỚNG DẪN] Cách Đếm Số Lượng Trong Excel Nhanh Và Chuẩn Nhất

Cách Đếm Số Lượng Trong Excel ra sao? Hàm COUNTIF trong Excel cho phép bạn đếm số lần xuất hiện của các giá trị cụ thể trong…

[TÌM HIỂU] Hàm If Else Trong Excel: Cách Sử Dụng Hàm Và Lưu Ý

Trong bài viết này Studyexcel sẽ hướng dẫn bạn cách sử dụng Hàm If Else Trong Excel. Mời bạn đọc cùng theo dõi! Cấu trúc IF ELSE…

[HƯỚNG DẪN] Cách Làm Lịch Trong Excel Chi Tiết Nhất

Để theo dõi và quản lý công việc, xây dựng lịch biểu là một sự lựa chọn hàng đầu. Bạn có thể tạo lịch hoặc sử dụng…

[TÌM HIỂU] Hàm Ifs Trong Google Sheet Và Những Điều Cần Biết

Hàm Ifs Trong Google Sheet là hàm có cách sử dụng tương tự hàm IF lồng. Nhưng cấu trúc của hàm IFS sẽ trông đơn giản và dễ…

[TÌM HIỂU] Hàm Căn Bậc 3 Trong Excel Và Những Điều Cần Biết

Excel là một phần mềm tin học văn phòng rất mạnh mẽ và đa dụng. Ngoài những phép tính cơ bản như cộng, trừ, nhân, chia, Excel…

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *