# [Note] Utilization of Microsoft excel functions for search and counting
###### tags: `SEARCH`, `ISNUMBER`, `SUMPRODUCT`
[toc]
Here is an example to show how to use excel functions such as *SEARCH*, *ISNUMBER* or *SUMPRODUCT* to get or count the data we want.
## Data
| Name | Nation | Age | Hight | Weight | Motto |
|-|-|-|-|-|-|
| Alice | China | 16 | 140 | 40 | When life gives you lemons, make lemonade. |
| Alice | America | 36 | 150 | 50 | I think I am |
| Jason | China | 24 | 170 | 66 | Your soul knows when it's on to something. |
| Kelly | India | 18 | 166 | 48 | Nothing will make you feel better except doing the work. |
| Tomas | Japan | 29 | 183 | 72 | Will this matter a year from now? |
| Alice | Cambodia | 44 | 154 | 60 | Life every day like it's your last. |
| Thompson | Turkey | 54 | 180 | 80 | Eat healthy, sleep well, breathe deeply, enjoy life. |
| Alice | Thailand | 32 | 172 | 52 | Everything in life that matters requires risk. |
## Single expression search and count
- Find how many Alice are there
```vb
1=COUNTIF(A2:A9,"Alice")
1=COUNTIF(A2,"Alice")
0=COUNTIF(A4,"Alice")
```
- Find how many people's name contains the substring son
```vb
2=SUMPRODUCT(--ISNUMBER(SEARCH("son",A2:A9)))
TRUE=(ISNUMBER(SEARCH("son",A9)))
FALSE=(ISNUMBER(SEARCH("son",A7)))
1=(--ISNUMBER(SEARCH("son",A9)))
0=(--ISNUMBER(SEARCH("son",A7)))
```
## Double expression search and count
- Find how many people's name contains the substring son and is a turkish
```vb
1=SUMPRODUCT(--ISNUMBER(SEARCH("son",A2:A9))*--ISNUMBER(SEARCH("Turkey",B2:B9)))
2=SUMPRODUCT(--ISNUMBER(SEARCH("son",A2:A9)))
1=SUMPRODUCT(--ISNUMBER(SEARCH("Turkey",B2:B9)))
```
- Find how many Alice from Asian are there
```vb
3=SUMPRODUCT(--ISNUMBER(SEARCH("Alice",A2:A9))*(--ISNUMBER(SEARCH("China",B2:B9))+--ISNUMBER(SEARCH("Thailand",B2:B9))+--ISNUMBER(SEARCH("Cambodia",B2:B9))))
1=SUMPRODUCT(--ISNUMBER(SEARCH("Alice",A2:A9))*--ISNUMBER(SEARCH("China",B2:B9)))
1=SUMPRODUCT(--ISNUMBER(SEARCH("Alice",A2:A9))*--ISNUMBER(SEARCH("Thailand",B2:B9)))
1=SUMPRODUCT(--ISNUMBER(SEARCH("Alice",A2:A9))*--ISNUMBER(SEARCH("Cambodia",B2:B9)))
```
## Triple expression search and count
- Find how many adult Alice from Asian are there
```vb
2=SUMPRODUCT(--ISNUMBER(SEARCH("Alice",A2:A9))*(--ISNUMBER(SEARCH("China",B2:B9))+--ISNUMBER(SEARCH("Thailand",B2:B9))+--ISNUMBER(SEARCH("Cambodia",B2:B9)))*((--C2:C9>20)))
6=SUMPRODUCT(--(C2:C9>20))
6=COUNTIF(C2:C9,">20")
```
## Quadruple expression Search
- Find how many adult Alice from Asian or height over 140 are there
```vb
3=SUMPRODUCT(--ISNUMBER(SEARCH("Alice",A2:A9))*(--ISNUMBER(SEARCH("China",B2:B9))+--ISNUMBER(SEARCH("Thailand",B2:B9))+--ISNUMBER(SEARCH("Cambodia",B2:B9)))*((--C2:C9>20)))
+SUMPRODUCT(--ISNUMBER(SEARCH("Alice",A2:A9))*(--ISNUMBER(SEARCH("China",B2:B9))+--ISNUMBER(SEARCH("Thailand",B2:B9))+--ISNUMBER(SEARCH("Cambodia",B2:B9)))*((--D2:D9>=140)))
-SUMPRODUCT(--ISNUMBER(SEARCH("Alice",A2:A9))*(--ISNUMBER(SEARCH("China",B2:B9))+--ISNUMBER(SEARCH("Thailand",B2:B9))+--ISNUMBER(SEARCH("Cambodia",B2:B9)))*(--C2:C9>20)*(--D2:D9>=140))
8=COUNTIF(D2:D9,">=140")
2=SUMPRODUCT(--ISNUMBER(SEARCH("Alice",A2:A9))*(--ISNUMBER(SEARCH("China",B2:B9))+--ISNUMBER(SEARCH("Thailand",B2:B9))+--ISNUMBER(SEARCH("Cambodia",B2:B9)))*((--C2:C9>20)))
3=SUMPRODUCT(--ISNUMBER(SEARCH("Alice",A2:A9))*(--ISNUMBER(SEARCH("China",B2:B9))+--ISNUMBER(SEARCH("Thailand",B2:B9))+--ISNUMBER(SEARCH("Cambodia",B2:B9)))*((--D2:D9>=140)))
2=SUMPRODUCT(--ISNUMBER(SEARCH("Alice",A2:A9))*(--ISNUMBER(SEARCH("China",B2:B9))+--ISNUMBER(SEARCH("Thailand",B2:B9))+--ISNUMBER(SEARCH("Cambodia",B2:B9)))*(--C2:C9>20)*(--D2:D9>=140))
```
Sign in with Wallet
Connect another wallet