Chia sẻ công thức Excel tính tổng các số trước ký hiệu trong chấm công

 Trong nhiều tình huống nhập liệu, đặc biệt là trong chấm công thì hay nhập liệu kết hợp cả số và chữ. Ví dụ: 6.5N - tức làm việc ca ngày 6.5 tiếng

Tình huống ví dụ: Các cột ngày được chấm với ký hiệu N đằng sau và trước đó là số giờ công

 Trường hợp 1: Tính tổng các số trước chữ N

Và tính cả các trường hợp sau:

- Có xuất hiện các ô trống trong vùng dữ liệu

- Số nhập vào trước chữ N thì có thể là số thập phân hoặc số nguyên

- Nếu không có chứa chữ N mà chỉ có con số thì mặc định là N

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

=SUMPRODUCT(--LEFT("0"&B2:J2&"N",FIND("N","0"&B2:J2&"N")-1))

Trường hợp 2: Tính tổng các số trước chữ N nhưng không tính ngày chủ nhật

Và tính cả các trường hợp như tình huống 1

=SUMPRODUCT(LEFT("0"&B2:J2&"N",FIND("N","0"&B2:J2&"N")-1)*(WEEKDAY(B$1:J$1)<>1))

Trong đó: B1:J1 là dòng chứa giá trị ngày tháng

Trường hợp 3: Trong dữ liệu có cả ký hiệu khác nhưng chỉ tính tổng các số trước chữ N

Công thức tính tính tổng tất cả các số trước chữ N:

=SUM(--IF(ISNUMBER(FIND("N",B2:J2)),LEFT(B2:J2,LEN(B2:J2)-1),0))

Nhấn Ctrl+Shift+Enter để chạy công thức

Công thức tính tổng tất cả các số trước chữ N và loại trừ chủ nhật

=SUM(--IF(ISNUMBER(FIND("N",B2:J2))*(WEEKDAY($B$1:$J$1)<>1),LEFT(B2:J2,LEN(B2:J2)-1),0))

Trong đó B1:J1 là dòng chứa giá trị ngày tháng. Nhấn Ctrl+Shift+Enter để chạy công thức.

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334