4 phương pháp đã chứng minh để tính tổng ô có màu trong Excel

Không có hàm tích hợp sẵn nào trong Excel tính tổng các ô có màu tự động. Tuy nhiên, có nhiều cách để tính tổng ô dựa trên màu nền của chúng. Trong bài viết này, bạn sẽ học 4 cách riêng biệt để tính tổng các ô có màu trong Excel với ví dụ dễ hiểu và minh họa rõ ràng.

Tải file thực hành

Bạn nên tải file Excel và thực hành cùng bài viết.

4 cách tính tổng ô có màu trong Excel

Chúng ta sẽ sử dụng bảng dữ liệu Danh sách giá sản phẩm để minh họa tất cả các phương pháp.

4 phương pháp đã chứng minh để tính tổng ô có màu trong Excel

Hãy bắt đầu ngay các phương pháp một bằng một.

1. Sử dụng hàm SUMIF để tính tổng ô có màu

Giả sử bạn muốn tính tổng giá các sản phẩm có "MTT" trong mã sản phẩm. Để đánh dấu các sản phẩm đó, bạn tô màu xanh cho chúng. Bây giờ chúng ta sẽ thảo luận công thức tính tổng giá trị các ô được tô màu xanh. Chúng ta có thể dùng hàm SUMIF. Làm theo các bước sau:

🔗 Các bước:

❶ Trước hết, thêm cột phụ để chỉ định màu ô trong cột "Price" (Giá).

❷ Chọn ô C16 để lưu kết quả công thức.

❸ Nhập công thức:

=SUMIF(E5:E13,"Blue",D5:D13)

❹ Cuối cùng nhấn ENTER.

4 phương pháp đã chứng minh để tính tổng ô có màu trong Excel

Đọc thêm: Cách tính tổng ô được chọn trong Excel (4 phương pháp dễ dàng)

2. Sử dụng AutoFilter và SUBTOTAL để cộng ô có màu

Chúng ta cũng có thể dùng tính năng AutoFilter và hàm SUBTOTAL để tính tổng ô có màu. Các bước thực hiện:

🔗 Các bước:

❶ Trước hết, chọn toàn bộ bảng dữ liệu.

❷ Vào tab Data (Dữ liệu).

❸ Nhấp vào lệnh Filter (Lọc).

4 phương pháp đã chứng minh để tính tổng ô có màu trong Excel

❹ Nhấp vào biểu tượng thả xuống ở góc tiêu đề cột Price.

❺ Từ menu thả xuống chọn Filter by Color (Lọc theo màu).

❻ Nhấp vào hình chữ nhật màu xanh.

4 phương pháp đã chứng minh để tính tổng ô có màu trong Excel

❼ Chọn ô C16 để lưu kết quả.

❽ Nhập công thức:

=SUBTOTAL(109,D5:D7)

❾ Nhấn ENTER để hoàn tất.

4 phương pháp đã chứng minh để tính tổng ô có màu trong Excel

Đã xong.

Đọc thêm: Cách tính tổng ô đã lọc trong Excel (5 cách phù hợp)

Bài đọc tương tự

Cách tính tổng theo nhóm trong Excel (4 phương pháp)
[Đã sửa!] Công thức SUM Excel không hoạt động và trả về 0 (3 giải pháp)
Cách chỉ tính tổng số dương trong Excel (4 cách đơn giản)
Tính tổng theo màu font trong Excel (2 cách hiệu quả)
Cách tính tổng phạm vi ô trong hàng dùng Excel VBA (6 phương pháp dễ dàng)

3. Sử dụng hàm GET.CELL của Excel để tính tổng ô có màu

Bạn có thể dùng hàm GET.CELL kết hợp hàm SUMIF để tính tổng ô có màu. Làm theo các bước dưới đây để thấy cách kết hợp chúng:

🔗 Các bước:

❶ Vào Formulas ▶ Defined Names ▶ Name Manager (Công thức ▶ Tên xác định ▶ Quản lý tên).

4 phương pháp đã chứng minh để tính tổng ô có màu trong Excel

Hộp thoại Name Manager sẽ bật lên. Từ hộp đó:

❷ Nhấp New (Mới).

4 phương pháp đã chứng minh để tính tổng ô có màu trong Excel

Hộp thoại Edit Name (Chỉnh sửa tên) xuất hiện. Từ đó:

❸ Đặt tên, ví dụ Code trong thanh Name.

❹ Nhập mã sau vào thanh Refers to (Tham chiếu đến):

=GET.CELL(38,GET.CELL!$D5)

❺ Nhấp OK.

4 phương pháp đã chứng minh để tính tổng ô có màu trong Excel

❻ Tạo cột mới. Cột Code như sau.

❼ Chọn ô E5 và nhập:

=Code

Nhấn ENTER.

4 phương pháp đã chứng minh để tính tổng ô có màu trong Excel

❽ Kéo Fill Handle đến cuối cột Code.

4 phương pháp đã chứng minh để tính tổng ô có màu trong Excel

❾ Chọn ô C16 và nhập công thức:

=SUMIF(E5:E13,33,D5:D13)

❿ Nhấn ENTER để kết thúc.

4 phương pháp đã chứng minh để tính tổng ô có màu trong Excel

Vậy là có kết quả!

␥ Phân tích công thức

=GET.CELL(38,GET.CELL!$D5) ▶ 38 tham chiếu đến phép tổng; GET.CELL! là tên sheet; $D5 là địa chỉ ô màu đầu tiên.
=Code ▶ là mã tổng hợp như đã tạo ở bước 7.
=SUMIF(E5:E13,33,D5:D13) ▶ tính tổng giá trị ô trong cột Price có mã màu 33.

Đọc thêm: Tính tổng đến cuối cột trong Excel (8 phương pháp tiện dụng)

4. Macro VBA Excel: Công cụ khác để cộng ô có màu

Bạn cũng có thể tính tổng ô có màu bằng mã VBA. Phần này sẽ tạo hàm do người dùng định nghĩa (UDF) bằng VBA để tính tổng ô có màu.

Làm theo các bước:

❶ Nhấn ALT+F11 để mở cửa sổ Excel VBA.

4 phương pháp đã chứng minh để tính tổng ô có màu trong Excel

❷ Vào Insert ▶ Module.

4 phương pháp đã chứng minh để tính tổng ô có màu trong Excel

❸ Sao chép mã VBA sau:

Function SumColoredCells(CC As Range, RR As Range) 
    Dim X As Long 
    Dim Y As Integer 
    Y = CC.Interior.ColorIndex 
    For Each i In RR 
        If i.Interior.ColorIndex = Y Then 
            X = WorksheetFunction.Sum(i, X) 
        End If 
    Next i 
    SumColoredCells = X 
End Function

❹ Dán và lưu mã này trong trình soạn thảo VBA.

4 phương pháp đã chứng minh để tính tổng ô có màu trong Excel

❺ Chọn ô D16 để lưu kết quả tổng.

❻ Nhập mã vào ô:

=SumColoredCells($D$5,D5:D13)

Mã này sẽ tính tổng tất cả ô được tô màu vàng.

❼ Nhấn ENTER.

4 phương pháp đã chứng minh để tính tổng ô có màu trong Excel

␥ Phân tích công thức

📌 Cú pháp =SumColoredCells(ô_màu_mẫu, phạm_vi)

$D$5 ▶ ô mẫu tô màu vàng.
D5:D13 ▶ phạm vi ô thực hiện phép tổng.

📓 Lưu ý:

Công thức tính tổng ô tô Xanh:

=SumColoredCells($D$8,D6:D14)

Trong đó ô $D$8 là ô mẫu tô Xanh.

Công thức tính tổng ô tô Cam:

=SumColoredCells($D$11,D7:D15)

Trong đó ô $D$11 là ô mẫu tô Cam.

Đọc thêm: Tính tổng ô trong Excel: Liên tục, Ngẫu nhiên, Có điều kiện, v.v.

Lưu ý

📌 Chú ý cú pháp các hàm.

📌 Nhập phạm vi dữ liệu cẩn thận vào công thức.

Kết luận

Tóm lại, chúng ta đã minh họa 4 phương pháp khác nhau để tính tổng ô có màu trong Excel. Bạn có thể tải file thực hành đính kèm và thử tất cả các phương pháp. Đừng ngần ngại hỏi đáp trong phần bình luận. Chúng tôi sẽ phản hồi sớm nhất.

Các bài viết liên quan

Cách chỉ tính tổng ô hiển thị trong Excel (4 cách nhanh)
Tổng tất cả khớp với VLOOKUP trong Excel (3 cách dễ dàng)
Cách tính tổng bình phương trong Excel (6 mẹo nhanh)
Tổng nếu ô chứa văn bản trong Excel (6 công thức phù hợp)
Cách tính tổng nhiều hàng trong Excel (4 cách nhanh)