One 、 Display the error value generated by the formula as null

The formula ：=IFERROR(C2/D2,"")

Two 、 Fuzzy summation

contain A The summation formula of ：=SUMIF(A2:A5,"*A*",C2:C5)

With A The sum formula at the beginning ：=SUMIF(A2:A5,"A*",C2:C5)

With A The summation formula at the end ：=SUMIF(A2:A5,"*A",C2:C5)

3、 ... and 、 Count the total number of people who don't repeat

The formula ：=SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11))

Four 、 String search

The formula ：=IF(COUNT(FIND(" jiangsu ",A2))=0," no "," yes ")

5、 ... and 、 Summation of separated columns

The actual total formula ：=SUMPRODUCT((MOD(COLUMN($C$3:$H$3),2)=1)*C3:H3)

The plan total formula ：=SUMPRODUCT((MOD(COLUMN($C$3:$H$3),2)=0)*C3:H3)

Formula analysis ：

First use Column Function to get the column number , Re pass Mod Module the column , Even columns divided by 2 The remainder is 0, Odd column divided by 2 The remainder is 1, Re pass Sumproduct Function for array operations , Sum after multiplication .

because mod The result is zero 1 and 0, So the formula is equal to 1 You can also omit , Optimize to ：=SUMPRODUCT((MOD(COLUMN($C$3:$H$3),2)*C3:H3))

6、 ... and 、 Automatically select qualified data to sum

The formula ：=SUMIF(A:A,E2,C:C)

7、 ... and 、 Multi cell string merging

The formula ：=PHONETIC(A1:A5)

8、 ... and 、 After interception 4 The part outside the bit

The formula ：=LEFT(C2,LEN(C2)-4)