Curly Brackets but not an array?

[Ally72]

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!

 

[BarryL]

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

 

[Ally72]

Fab - thanks so much @BarryL Really helpful.