Excel Array Formula

Knowing Excel array formulas will catapult you to ‘Guru Status’ in your office and I’ll do my best to make this painless, but you might need to get a brainbooster (a fruit or veg snack given to the 5 year olds at my son’s school at 10am each morning when they’re starting to lose focus).

Array formulas are ideal for summing or counting cells based on multiple criteria, a bit like SUMIF and SUMIFS, and COUNTIF and COUNTIFS but better, especially if you only have Excel 2003 and don’t have the *IFS functions.

And unlike the SUMIFS and COUNTIFS functions which only allow you to specify AND criteria, with array formulas you can specify OR criteria too.

Using the data in the table as an example the
SUMIFS is limited to this type of criteria:Excel array formula example data

Sum Sales if the salesperson is John AND the region is North

SUMIFS Formula:

=SUMIFS(Sales,Sales_Person,"John", Region,"North")

=142

Whereas with an array formula you can do this:

Sum Sales if the salesperson is John OR Jim AND the region is North OR South

Array Formula:

{=SUM((Sales)*((Sales_Person="John")+(Sales_Person="Jim"))*((Region="North")+(Region="South")))}

=306

We can see from the array formula example above that the * symbol instructs Excel to interpret the criteria as AND, and the + symbol is OR.

Note: the cell ranges in the formulas above have been replaced with named ranges to make it easier to read, quicker to build and easier to follow when you come back to the workbook months later.

Download the workbook and follow along or reverse engineer the formulas.

What is an Array?

An array is a series of data values, as opposed to a single value in one cell. Arrays can be contained in a single row, a column or multiple rows and columns. And array formulas can be entered in a single cell or a range of two or more cells. In this tutorial we’re going to look at single cell array formulas.

You will recognise an array formula in Excel because it is enclosed in curly brackets { } see example above. These curly brackets are inserted by pressing CTRL+SHIFT+ENTER upon entering the formula, and as a result array formulas are sometimes known as ‘CSE formulas’. 

Array Formula Example

Let’s take a look at what’s actually happening in Excel when we insert an array formula.

Excel array formulas are testing for TRUE or FALSE outcomes with a numerical equivalent for TRUE being 1, and for FALSE being 0. These TRUE or FALSE values are known as Boolean values.

Taking the above example Excel is finding whether the values in the salesperson column are either John or Jim, and awarding the cell a 1 or a 0 based on the outcome.
Excel array formula explained

So the Sales Person values are {1,1,0,1,1,0}

It then does the same for the Region:

{1,1,1,1,1,1}

And then multiplies these outcomes by the Sales column and adds up the result for each row:

{1x1x76}+{1x1x94}+{0x1x54}+{1x1x66}+{1x1x70}+{0x1x80} = 306

How to Enter an Array Formula

As mentioned earlier, array formulas are surrounded with curly brackets. You must not type in these curly brackets (if you do your formula won’t work). They must be entered by pressing CTRL+SHIFT+ENTER when you enter the formula, as opposed to just pressing ENTER.

You will notice the curly brackets disappear when you edit the cell. When you’re finished editing you need to press CTRL+SHIFT+ENTER again to enter the formula correctly.

Note: While the SUMPRODUCT function is an array formula it doesn’t require curly brackets, or to be entered using CTRL+SHIFT+ENTER.

Array Formula Examples

Using the sample data on the right let’s work Excel array formula examples
through some scenarios.

Note: Again I’ve used named ranges for each column (Employee, Date and Overtime) as you will see in the formulas below.

COUNTIF Using an Array Formula and a Date Range

If you have Excel 2007 or Excel 2010 you can do these with the COUNTIFS function.

Scenario 1

Count the number of overtime days worked per person for each month

Array Formula for May in cell S6

{=SUM((Employee=$R6)*
(Date>=DATEVALUE("1/05/2011"))*(Date<=DATEVALUE("31/05/2011")))}

Array Formula for June in cell T6

{=SUM((Employee=$R6)*
(Date>=DATEVALUE("1/06/2011"))*(Date<=DATEVALUE("30/06/2011")))}

Result
Excel array formula COUNTIF example 1

Scenario 2

Count the number of overtime days worked per person each month that were more than 1 hour

Array Formula for May in cell S18

{=SUM((Employee=$R18)*(Date<=DATEVALUE("1/05/2011"))*(Date>=DATEVALUE("31/05/2011")*(Overtime>1)))}

Array Formula for June in cell T18

{=SUM((Employee=$R18)*(Date<=DATEVALUE("1/06/2011"))*(Date>=DATEVALUE("30/06/2011")*(Overtime>1)))}

Result
Excel array formula COUNTIF example 2

AVERAGE Using Array Formulas

Scenario:

Average the overtime hours worked per person each month

If you have Excel 2007 or Excel 2010 you can do this with the AVERAGEIFS function.

Array formula in cell P30:

{=AVERAGE(IF((Employee=$O31)*(Date>=DATEVALUE("1/05/2011"))*(Date<=DATEVALUE("31/05/2011")),Overtime))}

Result
Excel array formula AVERAGEIF example

Note: When the IF function is used in an array formula it evaluates slightly differently. It creates an intermediate array using cell references for TRUE, and ‘FALSE’ values for FALSE.

For example an intermediate array might look like this:

{FALSE, FALSE, FALSE, FALSE, N10, N11}

Where N10 and N11 are TRUE i.e. they match the criteria. And since the average function in Excel is set to ignore Boolean values it will only average N10 and N11 ignoring the FALSE values thus creating a true AVERAGE.

Array Formula Rules

1.       The ranges referred to in your array formulas must be the same size, otherwise you will get an error. For example in the formula below you can see that although the formula may refer to different columns of data the length of the column is from row 6 to row 11 in each case.

{=SUM((C6:C11)*((A6:A11="John")+(A6:A11="Jim"))*((B6:B11="North")+(B6:B11="South")))}

2.       To enter the array formula you must press CTRL+SHIFT+ENTER, that is hold down CTRL and SHIFT and then press ENTER releasing all of them together. If you use a Mac it's COMMAND+RETURN.

3.       Do not enter the curly brackets yourself, Excel does it when you press CTRL+SHIFT+ENTER.

4.       When the multiplication symbol * is used it means AND, and when the plus symbol + is used it means OR.

Download the workbook and follow along or reverse engineer the formulas.

FREE PDF Download
100 Excel Tips & Tricks

Excel Tips & Tricks E-Book
Just enter your details below
* indicates required

We respect your email privacy

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current ye@r *

Comments

  1. K. D. Sumiit says

    Hi ! I was looking for a formula which helps in the following scenario :
    I got 5 sheets in a single work book- sheet 5 (master sheet) has to be compulsorily updated- other 4 sheets has to be updated with bits-n-pieces data 4m sheet 5. Is it possible 2 derive a formula that would help

  2. Lado says

    Dear Mrs. Linda,

    I was trying to download examples by clicking “Download the workbook”, but when I unzip file, there is only a folder structure with many xml files, so I can’t find any workbook to open it with excel.
    Please, advice me, how to do it, or make me know where do you have a reference page with explanation (if exists).
    Thank you in advance,
    Lado

    • Mynda Treacy says

      Hi Lado,

      That’s because it’s not a .zip file. Your browser is changing the file extension on download. If you hover your mouse over the download link you can see that it’s actually a .xlsx file.

      What you need to do is download it again but this time type over the .zip file extension with .xlsx at the ‘save as’ screen (or equivalent for your browser) and then when it has downloaded you can open it like any other Excel file.

      Let me know if you have any problems.

      Kind regards,

      Mynda.

    • Mynda Treacy says

      Hi Peter,

      The workbook is a .xlsx file. Try renaming it or download it again and make sure your browser doesn’t change the file extension. You can type over it with .xlsx in the ‘save as’ window.

      Kind regards,

      Mynda.

  3. Sorin says

    Hi Mynda,
    How can I use array into RANK function?
    In your example, I want calculate rank of sales person from its regions. I calculate all sales for each region and I calculate the percent for the sales person. For each sales person I have a percent of sales for his region. Now I want calculate rank of each sales person.
    The RANK function is: RANK(sales percent of person, all percent of his region). How do I do this dynamics? (Dynamic second argument of function.)
    My idea is simply but don’t work. I calculate in column D percent of each sales person from his region. Total of region is simply to calculate (sumif or sumproduct). I sort by region to have successive region. Now I have first of argument for RANK function and for second argument I try something like ($D$6:$D$11)*–($B$6:$B$11=B6). This return an array but I can’t use for second argument in RANK formula.
    Any idea? Any suggestion?
    Thank you.

    Best regards,
    Sorin.

  4. Vincent says

    I would like to get a multiplication value as per the following

    c1 having value (10) in sheet1 * c1 (20) in sheet2, if a1(“pet”) in sheet1 matches with a1(“Pet”) in sheet2 and give the result in d1 in sheet1.

    Kindly help me with the formula.

    Vincent

  5. Meggan says

    Reply to myself ;)

    So, with your formulas, I had to change the month-day order in the dates. And I had to tell it what I was summing — ie: I added (Overtime)* to make it {=SUM((Overtime)*(Employee=$O7)*(Date>=DATEVALUE(“6/1/2011″))*(Date<=DATEVALUE("6/30/2011")))}

    It appears the formula doesn't work if any cell in the range to be summed is text. Unfortunate since SUMIFS works, but does not allow for OR criteria.

    Any ideas?

    • Carlo Estopia says

      Hi Meggan,

      ;( :)
      Wooohkay… Honestly I don’t exactly know the answer. lol.
      However, If you need some crazy idea, It’s like this…
      SUMIF and SUM are all functions built some genius in the creators of Excel
      –no sarcasm there. They have tried to the best of their abilities
      to anticipate the anything-goes needs for people like us.
      SUMIF therefore was built to be very specific –hence no problem– while SUM/SUMPRODUCT
      are nice attempts of a do-it-all-almost functions. You can’t argue with SUMIF
      because it requires very specific arguments no if’s and no but’s. On the other hand,
      SUM allows complications particularly in an Array Formula. So perhaps the one who made the SUM function
      got tired of anticipating possible errors like adding a text and a number.
      He could have troubleshoot it but he decided : SUM are for numbers and not text. lol.

      Seriously, Some functions are so great in anticipating this while some are not.
      If you notice, the more difficult the task for a function is the more it is stricter as to its
      arguments. For example, SUM not in an array. It would have a logic like this:
      Loop each cells highlighted
      (if cell value is not a number change to ZERO)
      cell + cell
      result: SUM of Cells
      You see in programming(as Excel is an application, a program), Data types should be consistent
      especially numeric ones. You need logic or error handlers to avoid errors when you will be adding
      a text/string i.e. with a numeric one. It might look so simple on the surface but behind it are people
      who tried their best to anticipate and avoid these errors.

      Think of a very difficult task and maybe you’ll consider the creators of Excel to be human afterall. ;)

      Moral Lesson: Please do not ask the “why” of the matter. ;)

      Send your concern regarding the “how” and its possible workaround here HELP DESK.

      Cheers,

      CarloE

      • Meggan says

        Thanks for your reply, Carlo. =) If I had paid attention to the “Average” bit and to using an intermediate array, I wouldn’t have bothered you. Another good lesson for me, hahaha. Forcing that array lets me use text values in a column to be summed because they get dropped earlier than the SUM.

        This site has been super helpful; thanks for keeping it up!

  6. Meggan says

    Hi, I was excited to use these in a project. However, I can’t make it work — I always get #VALUE errors, even in the workbook I downloaded from this site.

    When I first downloaded and opened it, all the numbers in ‘Array Formulas’ O5:Q14 were there. I clicked in the formula, changed nothing, then used Ctrl+Shift+Enter to exit the formula; it resulted in the error. The curly brackets get added, but no values. =(

    Whaaa? Office Pro Plus 2010 on Win 7 Ent 7 SP1

  7. David says

    I’ve been looking for a way to concatenate/append arrays. I have used many programming languages with array capability that allow for concatenating/appending arrays.

    I have two arrays (as rages, or named arrays), and I want to be able to concatenate/append them into a single array for other manipulations. However, I have yet to find an Excel function/operator/whatever that provides for this functionality.

    In my present case, I have the simple situation of two one dimensional vertical arrays, and I want a single one dimensional vertical array formed from the elements of the first array followed by the elements of the second array.

    Is there a built-in function/operator/whatever to do this? (I know I could do it in Visual Basic, but I suspect that may slow my spreadsheet down even more than it already is.)

    • Carlo Estopia says

      Hi David,

      Try this formula.

      =IFERROR(INDEX(first,ROW(A1),1),"")&IFERROR(INDEX(jam,ROW(A1)),"") OR
      =IFERROR(INDEX(first,ROW(A1),1),"")&IFERROR(INDEX(jom,ROW(A1)),"")
      

      Note: first is a named range (single column)

      Named Range (A1:A6)
      a
      b
      c
      d
      e
      f
      

      jam is a named array horizontal (={“jay”,”wash”,”olsen”})
      jom is a named array vertical (={“jay”;”wash”;”olsen”})

      RESULTS:

        b      c
      ajay	ajay
      bwash	bwash
      colsen	colsen
      d	d
      e	e
      f	f
      

      *b is first and jam
      *c is first and jom
      similar results.

      The ROW function simply is an INDEX to the range and arrays respectively. So if you notice when you will
      try this experiment, the Row will increment from A1 to A2 etc. which will return values 1,2 etc. thus
      pointing to the right range/array row.

      Read more also on : Named Range, Index, Iferror

      Cheers.

      CarloE

  8. Pradeep says

    Hi Mynda,

    Just on question ( i know i will sound stupid after this), in above Countif example you have used named range as DATE. we have also a DATE formula in excel… will that cause any problem.

    Is it a good practice to avoid using names of excel formulas ?

    • Carlo Estopia says

      Hi Pradeep,

      You’re right using them isn’t a good practice.

      But if excel allows it, then it’s not totally
      a ‘bad’ practice’. One bad thing about
      this one is that it does create a little confusion
      when a user relies on the autocomplete feature of a formula
      because two ‘Dates’ will appear, the named range and
      the function Date. Even then, It’s not a big issue because
      in the final analysis the two can be distinguished. The
      function shows a screen tip (i.e. its arguments) while a
      named range doesn’t.

      Regarding VBA code, a named range is invoked with the use
      of double quotes (“”) very much like a string. So there’s
      no problem with that.

      So it’s up to the user whether to obey good practice or
      use meaningful names like ‘DATE’ for ones consumption.

      Most importantly, Excel stops the user from pursuing things
      which it really does not allow.

      See rules in naming a range.

      Cheers.

      CarloE

  9. Martin says

    Hi Mynda,

    I’m arriving a bit late to the party, but thought I’d offer an alternative to using the DATEVALUE formula. Instead I used TEXT(Date,”mmmm”) where Date is the named range. I set this equal to the cell with the month value. So the equivalent formula for:

    ‘{=SUM((Employee=$R6)*
    (Date>=DATEVALUE(“1/06/2011″))*(Date<=DATEVALUE("30/06/2011")))}

    is:
    ={SUM((Employee=$R6)*(TEXT(Date,"mmmm")=S$5))}

    Not sure if there are any disadvantages for this approach, but it works for me. I enjoyed the article – thanks for posting.

  10. Karine Bouffard says

    Hi Mynda,
    Should I use an array formula to return multiple vlookup results?
    See below ex. Say I want to retrieve each shop for article 1 in a separate column. I can use vlookup but it only returns the 1st found value. How do I retrieve the 2 shop names in 1 separate column? Is an array formula the solution?
    Article 1 Shop1
    Article 2 Shop2
    Article 1 Shop2
    Article 4 Shop3
    I hope you can help me. Thanks in advance.
    Karine

  11. Dwight Mallow says

    When I download your sample spreadsheets from the link above in ZIP format all I get are a lot of XLM files. What am I doing incorrecly?

    • says

      Hi Dwight,

      The problem you are having is your browser is changing the file extension to a .zip file when you download it. If you hover your mouse over the link and look in the bottom right/left of your browser you will see the correct file extension is a .xlsx

      You need to download the file again and at the ‘save as’ screen (or equivalent for your browser) type over the .zip file extension with .xlsx

      You will then be able to open the file in Excel as normal.

      Kind regards,

      Mynda.

  12. Paul Wyatt says

    Hello Mynda
    Now that I’ve been able to look at the full dataset, my query is resolved.
    The results reflect the number of entries in the list, rather than the overtime hours worked. Sorry to be a pest!

    Thanks and regards

    Hercules

  13. Paul Wyatt says

    Hello
    I suppose it must be me, but I couldn’t understand how you got the results for cells S6 and T6 from the sample data in the first example.
    The sample data doesn’t have any row/column refs, so I thought Id download the workbook and look at that. But… I couldn’t find any reference to the S6/T6 example in what was downloaded from the link.
    Also.. If I clicked on any of the tutorial links Excel (2010) told me that the operation was cancelled. As I am unable to view any of your examples in full, Im afraid that it isn’t any help to me!

    • says

      Hi Paul,

      Sorry you’re having trouble. If the links in the downloaded workbook aren’t working it’s usually because you haven’t enabled the workbook. When you download a workbook Excel disables it for your protection. There should be a yellow band above the formula bar with a notification asking you to ‘Enable Workbook’. You need to click this before the links will work.

      In the tutorial above there are no row or column references in the sample data because I have given them named ranges. You don’t need to know the references, just the named ranges.

      I hope that helps.

      Kind regards,

      Mynda.

  14. Vivek says

    Hi Mynda,

    Very neat and clean explanation of how an Array works. I have browsed many websites but yours is certainly at the top.

    Thanks.

    Regards,
    Vivek Sahasrabuddhey
    India

  15. Kumar says

    I trying to create formula in excel that text & numbers in next column having in each rows, but in between I don’t want one of the word or text & number of particular row.

    How to create a formula. Please help writing formula. Email to my ID which is mentioned. Below columns & rows.

    Sl.No. Items ABC XYZ PQR
    1 Pen 10 8
    2 pencil 12 4 8
    3 chocalate 24 18
    4 cream 8 6
    5 eraser 12 6 20

    • says

      Hi Kumar,

      I’m having difficulty understanding what you want. Can you please give me an example of the end result so I can see how to get from A to B and give you a solution.

      Kind regards,

      Mynda.

    • says

      Hi Chandra,

      If only it was that simple. There is no limit to the combinations of array formulas (well, statisticians would say there is a limit but it would be a ridiculously large number), so it really is a case of understanding how they work and then playing around with combinations of nested functions to get your desired results.

      I’m sorry I can’t give you an ‘array formula bible’ :(

      Kind regards,

      Mynda.

  16. Harlan says

    I have an array formula created. I don’t know how it got there, but it’s there now. When I try to do something as simple as change the value of a constant, I receive a #VALUE error.
    This is what I have entered in the cell:
    =B15+1.96*MMULT(MMULT(TRANSPOSE(H9:H11),B4:D6),H9:H11)^0.5

    Changing ANYTHING, even 1.96 to 1.95, returns the #VALUE error.

    Please advise.

    • Mynda Treacy says

      Hi Harlan,

      I can’t tell without seeing the workbook. You can send it to me via the Help Desk and I’ll take a look.

      Kind regards,

      Mynda.

  17. Raghu says

    Hi! Could you tell how to use the Vlookup function with Arrays.
    Further, i have been using the IF + ISERROR + VLOOKUP with COLUMNS Function extensively. Generally these files are of sized 5MB to 10MB size. However, when ever I run this formula, i find that, my system takes lot of time, some times it hangs up (not responding). Could you suggest, any better way?
    Thanks & regards
    Raghu A J M
    Hyderabad, India.

    • Mynda Treacy says

      Hi Raghu,

      There are (almost) endless ways to use a VLOOKUP in an array so I’d need an example of what you want to achieve before I can answer your question.

      Your IF + ISERROR + VLOOKUP + COLUMNS function is probably slow because (I suspect) you are requiring Excel to do the calculation twice. Once to find if the result is an error and a second time to find the result if it’s not. e.g. =IF(ISERROR(VLOOKUP(A1,$E$1:$G$100,3,FALSE)),””,VLOOKUP(A1,$E$1:$G$100,3,FALSE))

      You can try to use Database Functions where appropriate as an alternative.

      Also check out this post by Ozgrid. They cover in more detail the reason it’s not a good idea to use ISNA, or ISERROR in your case, with a VLOOKUP.

      I hope that helps.

      Kind regards,

      Mynda.

  18. Scott says

    THIS is THE BEST, MOST straightforward explanation on array formulas that I’ve seen on the web…THANK YOU for publishing it and demystifying it!!!

    • Mynda Treacy says

      :) Thanks again, Scott.

      I think Array formulas are quite a scary concept for loads of people (me included), but once you understand the logic and practice them a bit, you can start to think in terms of arrays, and they become a powerful weapon in your Excel arsenal.

      Cheers,

      Mynda.

Trackbacks