Skip to content

Instantly share code, notes, and snippets.

@YeeBePrime

YeeBePrime/excel-lambda-countIFArr.txt

Last active February 18, 2024 09:45
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save YeeBePrime/8f51b90d6f30200a6163ce664c7f5364 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save YeeBePrime/8f51b90d6f30200a6163ce664c7f5364 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. YeeBePrime revised this gist Oct 21, 2022. 1 changed file with 10 additions and 4 deletions.
    14 changes: 10 additions & 4 deletions excel-lambda-countIFArr.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -39,17 +39,22 @@ B6=2

    countIFArr=LAMBDA(ArraytoLook, LookFor,
    LET(
    _rng, ArraytoLook,
    _toLook, TOCOL(
    ArraytoLook
    ),
    _lookfor, TOCOL(
    LookFor
    ),
    SCAN(
    "",
    LookFor,
    _lookfor,
    LAMBDA(a, b,
    SUM(
    IF(
    ISERROR(
    FILTER(
    _rng,
    _rng =
    _toLook,
    _toLook =
    b
    )
    ),
    @@ -61,4 +66,5 @@ countIFArr=LAMBDA(ArraytoLook, LookFor,
    )
    )
    )

    ;
  2. YeeBePrime created this gist Oct 1, 2022.
    64 changes: 64 additions & 0 deletions excel-lambda-countIFArr.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,64 @@
    /*
    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(
    _rng, ArraytoLook,
    SCAN(
    "",
    LookFor,
    LAMBDA(a, b,
    SUM(
    IF(
    ISERROR(
    FILTER(
    _rng,
    _rng =
    b
    )
    ),
    0,
    1
    )
    )
    )
    )
    )
    )
    ;