Công thức chung
=SUMPRODUCT((WEEKDAY(dates)=day_num)*values)
Tóm lược
Để tính tổng dữ liệu theo ngày trong tuần (tức là tổng theo thứ Hai, thứ Ba, thứ Tư, v.v.), bạn có thể sử dụng hàm SUMPRODUCT cùng với hàm WEEKDAY.
Trong ví dụ được hiển thị, công thức trong H4 là:
=SUMPRODUCT((WEEKDAY(dates,2)=G4)*amts)
Giải trình
Bạn có thể thắc mắc tại sao chúng tôi không sử dụng hàm SUMIF hoặc SUMIFS? Đây dường như là một cách rõ ràng để tính tổng theo các ngày trong tuần. Tuy nhiên, nếu không thêm cột trợ giúp với giá trị ngày trong tuần, không có cách nào để tạo tiêu chí cho SUMIF có tính đến ngày trong tuần.
Thay vào đó, chúng tôi sử dụng hàm SUMPRODUCT tiện dụng, chức năng này xử lý các mảng một cách duyên dáng mà không cần sử dụng Control + Shift + Enter.
Chúng tôi đang sử dụng SUMPRODUCT chỉ với một đối số, bao gồm biểu thức sau:
(WEEKDAY(dates,2)=G4)*amts
Làm việc từ trong ra ngoài, hàm WEEKDAY được cấu hình với đối số tùy chọn 2, khiến nó trả về số 1-7 cho các ngày từ Thứ Hai đến Chủ Nhật, tương ứng. Điều này là không cần thiết, nhưng nó giúp bạn liệt kê các ngày theo thứ tự và lấy các số trong cột G theo thứ tự dễ dàng hơn.
WEEKDAY đánh giá từng giá trị trong phạm vi được đặt tên là "ngày tháng" và trả về một số. Kết quả là một mảng như thế này:
(3; 5; 3; 1; 2; 2; 4; 2)
Các số được trả về bởi WEEKDAY sau đó được so sánh với giá trị trong G4, là 1.
(3; 5; 3; 1; 2; 2; 4; 2) = 1
Kết quả là một mảng các giá trị TRUE / FALSE.
(FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE)
Tiếp theo, mảng này được nhân với các giá trị trong phạm vi được đặt tên là "amts". SUMPRODUCT chỉ hoạt động với các số (không phải văn bản hoặc boolean) nhưng các phép toán tự động ép buộc các giá trị TRUE / FALSE thành một và các số không, vì vậy chúng ta có:
(0; 0; 0; 1; 0; 0; 0; 0) * (100; 250; 75; 275; 250; 100; 300; 125)
Kết quả là:
(0; 0; 0; 275; 0; 0; 0; 0)
Chỉ với một mảng duy nhất này để xử lý, SUMPRODUCT tính tổng các mục và trả về kết quả.
