I don't know if you've noticed , We often see some formulas with “--”, A lot of people don't understand , This “--” What does it mean ？ What role does it play in the formula ？

Let's talk about it in detail today .

**“--” The role of ：**

“--” The core function of the system is to convert characters that cannot be calculated into numbers that can be calculated , So that the formula works properly , such as ：

- Convert text numbers to numeric format
- amount to n function , Put the logical value （True/False） Convert to number （1/0）

** Case study 1： Convert text numbers to numeric format **

Below C Column , There is a small green arrow in the upper left corner of the number cell , You can tell by the naked eye that these are numbers in text format , Can't participate in computation .

Without changing the cell format , Is it possible for these text numbers to participate in the calculation ？

** Solution 1：**

1. We use the most common sum Sum up the formula , See if it's possible to come out

2. Sure enough, it can't be calculated , I want to be blind

3. But let's modify the formula a little bit , As shown below , Add... Before the sequence of cells “--”--> And press at the same time Ctrl+Shift+Enter Three key enter , The problem is solved ：

=SUM(--C2:C15)

* Please note that ： Use Sum Function time , You need to press at the same time Ctrl+Shift+Enter Triple bond , Call the array formula ; If you use sumproduct function , Just go straight back

** Case study 2： amount to n function , Put the logical value （True/False） Convert to number （1/0）**

Or use this watch , We need to count the number of students in class one .

** Solution 2：**

1. Usually , It can be used as follows sumproduct+n Function to count the number of people in a class ：

=SUMPRODUCT(N(" Class one "=$A$2:$A$15))

Definition of formula ：

- n The function here is to convert the logical value of the conditional operation in brackets true/false Convert to numeric 1/0
- Reuse sumproduct Sum to get the result

of n For a detailed explanation of the function, see Excel function (11)–n Function to numeric

2. If you remove n function , The statistical result is 0, Because logical values cannot participate in summation

3. But we can use “--” To replace n function , The formula is as follows ：

=SUMPRODUCT(--(" Class one "=$A$2:$A$15))

Definition of formula ：

- “--” The effect in this case is exactly the same as n function , the true/false Convert to 1/0
- In the last case, I said ,sumproduct Function coordination “--” Words , You don't need to use array functions , Just go back