Last active
February 18, 2024 09:45
-
-
Save YeeBePrime/8f51b90d6f30200a6163ce664c7f5364 to your computer and use it in GitHub Desktop.
CountIF function that can be used with array/range using Excel lambda function.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| countIFArr | |
| Description | |
| CountIF that can be used with array/range. | |
| Syntax | |
| countIFArr(ArraytoLook, LookFor) | |
| The countIFArr function syntax has the following arguments: | |
| ArraytoLook Required. The array/range where you want to look. | |
| LookFor Required. The array/range you want to look for. | |
| Return count number for each item you look for. | |
| Example | |
| A2= Tokyo | |
| A3= Bangkok | |
| A4= Tokyo | |
| A5= Texus | |
| A6= Bangkok | |
| B2=countIFArr(A2:A6, A3) | |
| Return 2 | |
| B2=countIFArr(A2:A6&" City", A2:A6&" City") | |
| B2=2 | |
| B3=2 | |
| B4=2 | |
| B5=1 | |
| B6=2 | |
| */ | |
| countIFArr=LAMBDA(ArraytoLook, LookFor, | |
| LET( | |
| _toLook, TOCOL( | |
| ArraytoLook | |
| ), | |
| _lookfor, TOCOL( | |
| LookFor | |
| ), | |
| SCAN( | |
| "", | |
| _lookfor, | |
| LAMBDA(a, b, | |
| SUM( | |
| IF( | |
| ISERROR( | |
| FILTER( | |
| _toLook, | |
| _toLook = | |
| b | |
| ) | |
| ), | |
| 0, | |
| 1 | |
| ) | |
| ) | |
| ) | |
| ) | |
| ) | |
| ) | |
| ; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment