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.

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 45 comments… read them below or add one }

K. D. Sumiit January 5, 2014 at 1:08 am

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

Reply

Catalin Bombea January 5, 2014 at 8:56 pm

Hi Sumiit,
Can you please upload a sample of your workbook, with detailed descriptions of what you want to do? You can use our Help Desk: http://www.myonlinetraininghub.com/helpdesk/
Catalin

Reply

Lado October 2, 2013 at 7:10 pm

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

Reply

Mynda Treacy October 2, 2013 at 8:11 pm

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.

Reply

Peter Haviernik August 9, 2013 at 11:14 pm

Hi,
I tried to download the workbook, but all files are xml, not excel.

Reply

Mynda Treacy August 9, 2013 at 11:18 pm

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.

Reply

Sorin July 19, 2013 at 7:25 pm

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.

Reply

Mynda Treacy July 20, 2013 at 3:35 pm

Hi Sorin,

Have you read this post on different ways you can RANK data?

If that’s not what you need can you please send me your workbook via the help desk so I can see what you’re working with.

Thanks,

Mynda.

Reply

Sorin July 22, 2013 at 8:16 pm

Hi Mynda,
Your post with RANK is all I need! :-) I made some change and it’s work.
I didn’t saw the post before ask the question.
Thank you very much.

Best regards,
Sorin.

Reply

Mynda Treacy July 23, 2013 at 7:38 am

Great :) Glad it helped.

Reply

Vincent May 17, 2013 at 3:45 am

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

Reply

Mynda Treacy May 17, 2013 at 7:13 pm

Hi Vincent,

I think this is what you mean:

=IF(AND(Sheet1!A1="pet",Sheet2!A1="pet"),Sheet1!C1*Sheet2!C1,0)

Kind regards,

Mynda.

Reply

Meggan April 17, 2013 at 7:51 am

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?

Reply

Carlo Estopia April 17, 2013 at 7:19 pm

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

Reply

Meggan April 17, 2013 at 11:53 pm

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!

Reply

Carlo Estopia April 18, 2013 at 1:57 pm

Hi Meggan,

You’re welcome.

Cheers,

CarloE

Reply

Meggan April 17, 2013 at 7:26 am

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

Reply

David February 27, 2013 at 4:51 am

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.)

Reply

Carlo Estopia February 27, 2013 at 4:55 pm

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

Reply

Pradeep February 8, 2013 at 5:19 pm

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 ?

Reply

Carlo Estopia February 8, 2013 at 6:57 pm

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

Reply

Martin January 24, 2013 at 6:49 pm

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.

Reply

Mynda Treacy January 24, 2013 at 9:09 pm

Hi Martin,

Love it! I might share that in a future blog post :)

Cheers,

Mynda.

Reply

Karine Bouffard December 13, 2012 at 1:26 am

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

Reply

Mynda Treacy December 13, 2012 at 9:19 pm

Hi Karine,

Yes, you can use an array formula to lookup and return multiple values. Here is a tutorial on this exact topic.

Kind regards,

Mynda.

Reply

Dwight Mallow December 1, 2012 at 7:06 am

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?

Reply

Mynda Treacy December 1, 2012 at 7:49 am

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.

Reply

Paul Wyatt November 17, 2012 at 4:15 am

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

Reply

Paul Wyatt November 16, 2012 at 8:21 am

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!

Reply

Mynda Treacy November 16, 2012 at 9:05 am

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.

Reply

Vivek November 1, 2012 at 1:54 pm

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

Reply

Mynda Treacy November 1, 2012 at 8:26 pm

Cheers, Vivek. It’s rewarding to know I’ve been able to help :)

Reply

Kumar September 25, 2012 at 4:37 am

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

Reply

Mynda Treacy September 25, 2012 at 9:41 pm

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.

Reply

ChandraShekher Vishwakarma September 21, 2012 at 2:11 am

Please provide me the details of all array formulas.

Thanks
Chandra

Reply

Mynda Treacy September 21, 2012 at 8:20 am

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.

Reply

Harlan June 21, 2012 at 6:05 am

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.

Reply

Mynda Treacy June 21, 2012 at 6:56 am

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.

Reply

Raghu May 13, 2012 at 9:31 pm

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.

Reply

Mynda Treacy May 14, 2012 at 10:58 pm

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.

Reply

Scott Munro April 12, 2012 at 10:21 pm

The website very herlpfull to beginners

Reply

Mynda Treacy April 17, 2012 at 9:42 pm

Cheers, Scott.

Reply

Samwel Kitumbo February 22, 2012 at 3:12 pm

Thanks for the materials you have been offering. It keeps me updated with various tools of excel!

Reply

Scott February 5, 2012 at 7:42 am

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!!!

Reply

Mynda Treacy February 5, 2012 at 8:18 pm

:) 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.

Reply

Previous post:

Next post: