Excel đã phát triển công cụ mạnh mẽ và hữu ích để cộng giá trị theo nhiều điều kiện, như hàm SUMIF và SUMIFS. Nhưng để tìm tổng các giá trị từ các ô chứa một đoạn mã giống nhau, bạn sẽ sử dụng công thức nào? Đây là một câu hỏi rất thú vị mà nhiều độc giả đã gửi đến Mytour. Hãy khám phá cách làm trong bài viết dưới đây nhé.
Áp dụng hàm SUMIF và tạo cột phụ
Hãy xem xét ví dụ cụ thể sau đây:
Bước 1: Sử dụng 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 đề là Áo sơ mi và Nữ.
(2) Trên cột điều kiện Áo sơ mi, đặt tại ô H3, nhập công thức: H3 =IFERROR(SEARCH('Áo sơ mi',D3),0). Điều này 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. Nếu không có kí tự nào thỏa mãn điều kiện, sẽ trả về vị trí 0. Sao chép công thức cho toàn bộ các ô trong cột để có kết quả như sau:
(3) Tương tự với cột Nữ, thay đổi điều kiện từ Áo sơ mi sang Nữ để thu được kết quả:
Vì vậy, những hàng có mã thỏa mãn điều kiện sẽ có giá trị lớn hơn 0 trong cột phụ.
Bước 2: Tổng các hàng có giá trị lớn hơn không trong cột phụ sử dụng hàm SUMIF.
Để tổng Doanh thu theo đ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ự ở ô K4, nhập công thức K4 =SUMIF(I3:I12,'>0',G3:G12) để tổng Doanh thu thỏa mãn điều kiện sản phẩm chứa mã kí tự là nữ.
Chú ý: Trong quá trình thực hiện, Excel không phân biệt kí tự là chữ in hoa hay chữ thường.
Sử dụng hàm SUMIF và ký tự đại diện
Nếu bạn không muốn thêm cột phụ, có thể kết hợp với dấu sao (*) - ký tự đại diện cho bất kỳ kí tự nào trong 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). Thay đổi mục in nghiêng theo nhu cầu của bạn.
Đối với ví dụ tương tự như trước, Mytour đề xuất cách gõ công thức như sau: K3 =SUMIF(D3:D12,'*'&'Áo sơ mi'&'*',G3:G12), K4 =SUMIF(D3:D12,'*'&'nữ'&'*',G3:G12).
Kết quả thu được là:
Chú ý: Để sao chép công thức một cách hiệu quả, 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. Thay thế công thức 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 công cụ đặc biệt của Excel để xử lý dữ liệu mảng.
SUMPRODUCT sử dụng các phạm vi ô làm đối số, nhân và cộng các mục tương ứng trong các mảng để tổng kết quả.
Trong ví dụ này, 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 +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 chứa kí tự áo sơ mi và trả về giá trị lớn hơn 0, nếu không sẽ trả về 0.
- Khi đặt công thức này vào hàm SUMPRODUCT, Excel sẽ kiểm tra kết quả có >0 hay không, và tổng hợp doanh thu tương ứng trong cột Doanh thu (Cột G) nếu giá trị >0.
Lưu ý: Trong trường hợp này, bạn cần sử dụng tổ hợp phím Ctrl +Shif + Enter khiến công thức SUMPRODUCT trở nên đặc biệt.
Tương tự như với ô K4, Mytour dùng cách tương tự để tính tổng doanh thu từ các mặt hàng chứa kí tự nữ. Chúc các bạn thành công!