Để tính tổng tích các số tương ứng trong một hoặc nhiều phạm vi, hãy sử dụng hàm SUMPRODUCT mạnh mẽ của Excel.

Sử dụng cơ bản

  1. Ví dụ: hàm SUMPRODUCT bên dưới tính tổng số tiền đã chi tiêu.

Sumproduct Function in Excel

Giải thích: hàm SUMPRODUCT thực hiện phép tính này: (2 1000) + (4 250) + (4 100) + (2 50) = 3500.

  1. Các phạm vi phải có cùng kích thước nếu không Excel sẽ hiển thị lỗi #VALUE! lỗi.

Ranges of Unequal Size

  1. Hàm SUMPRODUCT xử lý bất kỳ mục nhập nào không phải là số như thể chúng là số không.

Non Numeric Entries

  1. Nếu bạn cung cấp một dải ô duy nhất, hàm SUMPRODUCT sẽ tạo ra kết quả chính xác giống như hàm SUM.

One Range

Advanced Use

Countif Function

1b. Hàm SUMPRODUCT bên dưới tạo ra cùng một kết quả.

Double Negative

Giải thích: – (A1: A7 = “star”) giảm thành hằng số mảng sau:

  • \ {TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE} Phủ định kép – buộc các Boolean này thành 1 và 0 (TRUE = 1, FALSE = 0). Kết quả:

\ {1; 0; 0; 0; 0; 1; 0} Hằng số mảng này được dùng làm đối số cho hàm SUMPRODUCT, cho kết quả là 2.

1c. Hàm COUNTIF bên dưới đếm số ô chứa chính xác dấu sao + 1 ký tự. Dấu chấm hỏi (?) Khớp đúng một ký tự.

Wildcard Character

1ngày. Hàm SUMPRODUCT không hoàn hảo! Bạn không thể sử dụng ký tự đại diện (? Và) khi bạn sử dụng hàm SUMPRODUCT.

Sumproduct Limitation

2a. Công thức mảng bên dưới đếm số ký tự trong một phạm vi ô.

Count Characters

Lưu ý: kết thúc công thức mảng bằng cách nhấn CTRL + SHIFT + ENTER. Excel thêm dấu ngoặc nhọn \ {}.

2b. Hàm SUMPRODUCT bên dưới tạo ra cùng một kết quả.

Elegant Sumproduct Formula

Lưu ý: hằng số mảng \ {9; 4; 6; 5} được sử dụng làm đối số cho hàm SUMPRODUCT, cho kết quả là 24. Hàm SUMPRODUCT xử lý mảng nguyên bản nên bạn không cần phải kết thúc bằng cách nhấn CTRL + SHIFT + VÀO. Đây là một trong những ưu điểm lớn nhất của hàm SUMPRODUCT vì dấu ngoặc nhọn \ {} khiến nhiều người mới bắt đầu sử dụng Excel bối rối! 3a. Công thức mảng dưới đây tính tổng doanh số bán hàng trong năm 2018.

Sum by Year

3b. Hàm SUMPRODUCT bên dưới tạo ra cùng một kết quả.

Multiplication Operator

Giải thích: (YEAR (A1: A5) = 2018) B1: B5 giảm thành:

(\ {2018; 2017; 2018; 2017; 2017} = 2018) \ {10; 1; 7; 20; 3} và điều này giảm thành:

\ {TRUE; FALSE; TRUE; FALSE; FALSE} \ {10; 1; 7; 20; 3} Chúng ta không cần số âm kép (xem ví dụ 1b) vì toán tử nhân * tự động ép các Boolean thành 1 và 0 (TRUE = 1, FALSE = 0). Kết quả:

\ {10; 0; 7; 0; 0} Hằng số mảng này được dùng làm đối số cho hàm SUMPRODUCT, cho kết quả là 17.