Cách tính tổng các mã giống nhau trong Excel một cách hiệu quả

Excel cung cấp những công cụ mạnh mẽ như hàm SUMIF và SUMIFS để tính tổng giá trị dựa trên một hoặc nhiều điều kiện. Tuy nhiên, khi cần tính tổng các giá trị mà các ô chứa cùng một đoạn mã, bạn cần áp dụng công thức nào? Đây là câu hỏi thú vị được nhiều độc giả gửi đến Tripi. Hãy khám phá cách thực hiện chi tiết trong bài viết dưới đây.

Sử dụng hàm SUMIF kết hợp với cột phụ

Hãy cùng xem xét ví dụ cụ thể sau đây:

Bước 1: Tạo cột phụ để xác định các dòng chứa ký tự thỏa mãn điều kiện.

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

(2) Trong cột điều kiện Áo sơ mi, cụ thể tại ô H3, nhập công thức: H3 =IFERROR(SEARCH("Áo sơ mi",D3),0). Công thức này giúp Excel tìm kiếm ký tự Áo sơ mi trong ô D3 và trả về vị trí của ký tự đó. Nếu không tìm thấy, kết quả trả về là 0. Sao chép công thức này cho toàn bộ cột để thu được kết quả như sau:

(3) Áp dụng tương tự với cột Nữ, thay điều kiện từ Áo sơ mi thành Nữ và thu được kết quả:

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

Bước 2: Sử dụng hàm SUMIF để tính tổng các dòng có giá trị lớn hơn 0 trong cột phụ.

Để tính tổng Doanh thu với điều kiện sản phẩm là Áo sơ mi, tại ô K3, nhập công thức: K3 =SUMIF(H3:H12,">0",G3:G12).

Tương tự, tại ô K4, nhập công thức K4 =SUMIF(I3:I12,">0",G3:G12) để tính tổng Doanh thu cho các sản phẩm có mã ký tự nữ.

Lưu ý: Với phương pháp trên, Excel không phân biệt chữ in hoa hay in thường.

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

Nếu bạn không muốn thêm cột phụ, có thể sử dụng ký tự đại diện (*) để đại diện cho bất kỳ ký tự nào với công thức sau:

=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). Hãy thay thế các phần in nghiêng phù hợp với dữ liệu của bạn.

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

Kết quả thu được như sau:

Lưu ý: Để sao chép công thức dễ dàng, hãy sử dụng tham chiếu tuyệt đối để cố định mảng tham chiếu và vùng tính tổng. Công thức được thay thế thành
K3=SUMIF($D$3:$D$12,"*"&J3&"*",$G$3:$G$12), trong đó J3 là ô chứa chuỗi ký tự cần tìm. Kết quả vẫn giữ nguyên.

Sử dụng hàm SUMPRODUCT để tính toán linh hoạt

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

SUMPRODUCT sử dụng các phạm vi ô làm đối số, nhân các phần tử trong các mảng với nhau và trả về tổng của các kết quả.

Áp dụng vào 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)), sau đó nhấn Ctrl + Shift + Enter.

• Trong công thức này, hàm IFERROR(SEARCH("áo sơ mi", D3:D12), 0) sẽ tìm kiếm các ô trong cột D chứa ký tự áo sơ mi và trả về giá trị lớn hơn 0 nếu tìm thấy, ngược lại trả về 0.
• Khi kết hợp với hàm SUMPRODUCT, Excel sẽ kiểm tra xem kết quả có lớn hơn 0 hay không và tính tổng doanh thu tương ứng trong cột G.

Lưu ý: Mặc dù SUMPRODUCT thường là công thức mảng không yêu cầu tổ hợp phím Ctrl + Shift + Enter, nhưng trong trường hợp này, bạn bắt buộc phải sử dụng tổ hợp phím này để đảm bảo kết quả chính xác.

Áp dụng cách làm tương tự cho ô K4, Tripi đã tính được tổng doanh thu cho các mặt hàng chứa ký tự nữ. Chúc các bạn thực hiện thành công!