Curly Brackets but not an array?

Ally72

New Member
Joined
Mar 18, 2010
Messages
39
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:

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,421
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,859
Messages
5,489,309
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top