Curly Brackets but not an array?
Results 1 to 3 of 3

Thread: Curly Brackets but not an array?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2010
    Location
    Colchester, Essex, UK
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Curly Brackets but not an array?

    Hi

    I've been given a spreadsheet to work through and there's a formula that I've never seen before.

    It's
    =IF(SUM(COUNTIF(E3,{"*M*","*T*","*W*","*F*"})),"O","")

    The curly brackets are there whether Ctrl Shift and Enter is used or not. The only time I've seen curly brackets are for arrays and they go round the outside.

    The M, T etc are referring Monday, Tuesday / Thursday, Wednesday and Friday.

    Can anyone help decipher this please? I know what it's doing per se, and have tried to replicate using OR just to make sure, but haven't managed it yet!
    Last edited by Ally72; Dec 18th, 2018 at 07:14 AM.

  2. #2
    Board Regular
    Join Date
    Jan 2014
    Location
    Dublin, Ireland
    Posts
    1,421
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Curly Brackets but not an array?

    The curly brackets in this case is an array for your criteria. Putting SUM prior to the COUNTIF allows the COUNTIF to perform its duties on all criteria within the curly brackets.

    For example, it will first perform the COUNTIF with a criteria of "*M*" (the asterisk here being a wildcard meaning that text can be present on either side but can also be stand-alone).

    It will then perform the COUNTIF with "*T*" (the asterisk doing the same thing here again)

    and so on...

    Then it will SUM the values of all these COUNTIF's to give you the value.

    If I had text in cell E3 of "123M456", the first COUNTIF would give me a value of 1, the succeeding 3 other COUNTIF's ("*T*" ,"*W*","*F*") would give me 0,0,0 respectively. Sum that up you will get 1.

    IF I had "M43754F5" in cell E3, i would get 1,0,0,1 . SUM that up and you will get 2.

    If you want to see how it works better, go to the Formulas tab on the ribbon and press Evaluate Formula (with the cell selected)

    HTH
    Learn something new everyday.

    be sure to use code tags

    Code:
    [ code ]
    [ / code ]
    ' no spaces

  3. #3
    New Member
    Join Date
    Mar 2010
    Location
    Colchester, Essex, UK
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Curly Brackets but not an array?

    Fab - thanks so much @BarryL Really helpful.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •