How Do I Know I Have an Array Formula?

bencar

Banned user
Joined
Jun 8, 2016
Messages
149
How do i know I have an array formula? For example, how do I know when to add brackets around this array formula?
{B2:I2*B3:BI}

And also with this?
{=SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))}
 
Last edited:

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
Notice that when you enter the formulas without holding down ctrl-shift usually only the first cell of the array is calculated.
 

dave2018

Board Regular
Joined
Mar 20, 2018
Messages
223
An Array formula is a formula that does calculations on many cells at once. For example, =A1="jim" Takes cell A1 and sees if it is equal to "jim". The array formula of =A1:A10="jim" Means that excel looks at A1, is it equal? then A2, is it equal etc. until the end.

In such a case, you would need to add curly brackets with CTRL+SHIFT+ENTER to tell excel that u want it to calculate all the cells in the range (and not just 1 cell..)

Disclaimer: sometimes, you can get around pressing CTRL+SHIFT+ENTER!!! See the following formulas:

<b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #B6AAA6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #201116"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #201116;text-align: center;">1</td><td style=";">Jim</td><td style="text-align: right;;">Without CSE (Bad)-----></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #201116;text-align: center;">2</td><td style=";">Adam</td><td style="text-align: right;;">With CSE (Good)-----></td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #201116;text-align: center;">3</td><td style=";">Ben</td><td style="text-align: right;;">Without CSE (Good)-----></td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #201116;text-align: center;">4</td><td style=";">Adam</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #201116;text-align: center;">5</td><td style=";">Jim</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #201116;text-align: center;">6</td><td style=";">Adam</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #201116;text-align: center;">7</td><td style=";">Ben</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #201116;text-align: center;">8</td><td style=";">Adam</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #201116;text-align: center;">9</td><td style=";">Jim</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #201116;text-align: center;">10</td><td style=";">Adam</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #B6AAA6;border-top:none;text-align: center;background-color: #E0E0F0;color: #201116">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #B6AAA6"><thead><tr style=" background-color: #E0E0F0;color: #201116"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">C1</th><td style="text-align:left">=SUM(<font color="rgb(255">--(<font color=" 0">A1:A10="Jim"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">C3</th><td style="text-align:left">=SUMPRODUCT(<font color="rgb(255">--(<font color=" 0">A1:A10="Jim"</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #B6AAA6"><thead><tr style=" background-color: #E0E0F0;color: #201116"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #201116">C2</th><td style="text-align:left">{=SUM(<font color="rgb(255">--(<font color=" 0">A1:A10="Jim"</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

PS: beginning to learn array formulas is like learning a new excel! But you get used to it pretty quickly with focus and consistency.

Also, U need to check out Mike ExcelIsFun Girvin on youtube! He has playlists of videos just on array formulas and functions. He also wrote a book called CTRL+SHIFT+ENTER (which explains all about array formulas - the only book of its kind, the way he lists it out methodically etc.)

And of course, if u have any more q's, u can probably count on MrExcel Forum! :)

Good Luck!
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

How do i know I have an array formula? For example, how do I know when to add brackets around this array formula?
{B2:I2*B3:BI}

And also with this?
{=SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))}

If you have bits with

[1] pairwise multiplications like

(A2:A4)*(B2:B4)

(A2:A4="jim")*(B2:B4)

[2] with IF(s) which act(s) as filter like

IF(A2:A4>=2,1)

[3] with multi-valued references which serve as look up value or comparison value like...

MATCH(A2:A4,B2:B4,0)

A2:A4=B2:B4

you have to do with an array-processing formula.

The foregoing list is probably not exhaustive. The gist is: bits leading to multi-value results indicate array-processing, therefore control+shift+enter if the receiving function is not designed exlusively for array-objects like SUMPRODUCT.

Hope this helps.
 

dave2018

Board Regular
Joined
Mar 20, 2018
Messages
223
If you have bits with

[1] pairwise multiplications like

(A2:A4)*(B2:B4)

(A2:A4="jim")*(B2:B4)

[2] with IF(s) which act(s) as filter like

IF(A2:A4>=2,1)

[3] with multi-valued references which serve as look up value or comparison value like...

MATCH(A2:A4,B2:B4,0)

A2:A4=B2:B4

you have to do with an array-processing formula.

The foregoing list is probably not exhaustive. The gist is: bits leading to multi-value results indicate array-processing, therefore control+shift+enter if the receiving function is not designed exlusively for array-objects like SUMPRODUCT.

Hope this helps.

Another Disclaimer, that even SUMPRODUCT can need CSE (CTRL+SHIFT+ENTER) because a function inside of the sumproduct may need CSE. so it's not 100% clear cut, which may be 1 of the reasons microsoft doen't really provide documentation for it so much...
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119

ADVERTISEMENT

Very confused by your answer. Would you further explain??

Aladin and Dave explained it more thoroughly, but here's another example:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">HXN</td><td style=";">GEH</td><td style=";">TZD</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">jim</td><td style=";">ben</td><td style=";">alice</td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">30</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">WOA</td><td style=";">ben</td><td style=";">BAK</td><td style="text-align: right;;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">45</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">FXN</td><td style=";">BUW</td><td style=";">EIG</td><td style="text-align: right;;">71</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">jim</td><td style=";">JXP</td><td style=";">alice</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">KPU</td><td style=";">ben</td><td style=";">RTH</td><td style="text-align: right;;">83</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">JCH</td><td style=";">LO</td><td style=";">LZB</td><td style="text-align: right;;">50</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">jim</td><td style=";">ben</td><td style=";">alice</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">jim</td><td style=";">UBJ</td><td style=";">ICO</td><td style="text-align: right;;">85</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">jim</td><td style=";">JCR</td><td style=";">HIF</td><td style="text-align: right;;">54</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=SUM(<font color="Blue">(<font color="Red">A1:A10="jim"</font>)*(<font color="Red">B1:B10="ben"</font>)*(<font color="Red">C1:C10="alice"</font>)*(<font color="Red">D1:D10</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G3</th><td style="text-align:left">{=SUM(<font color="Blue">(<font color="Red">A1:A10="jim"</font>)*(<font color="Red">B1:B10="ben"</font>)*(<font color="Red">C1:C10="alice"</font>)*(<font color="Red">D1:D10</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

without CSE only the first number is returned, with CSE you get sum({30,15})
 
Last edited:

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
If you have bits with

[1] pairwise multiplications like

(A2:A4)*(B2:B4)

(A2:A4="jim")*(B2:B4)

[2] with IF(s) which act(s) as filter like

IF(A2:A4>=2,1)

[3] with multi-valued references which serve as look up value or comparison value like...

MATCH(A2:A4,B2:B4,0)

A2:A4=B2:B4

you have to do with an array-processing formula.

The foregoing list is probably not exhaustive. The gist is: bits leading to multi-value results indicate array-processing, therefore control+shift+enter if the receiving function is not designed exlusively for array-objects like SUMPRODUCT.

Hope this helps.

Nice explanation Aladin. I'm book marking it. :)
 

bencar

Banned user
Joined
Jun 8, 2016
Messages
149
If you have bits with

[1] pairwise multiplications like

(A2:A4)*(B2:B4)

(A2:A4="jim")*(B2:B4)

[2] with IF(s) which act(s) as filter like

IF(A2:A4>=2,1)

[3] with multi-valued references which serve as look up value or comparison value like...

MATCH(A2:A4,B2:B4,0)

A2:A4=B2:B4

you have to do with an array-processing formula.

The foregoing list is probably not exhaustive. The gist is: bits leading to multi-value results indicate array-processing, therefore control+shift+enter if the receiving function is not designed exlusively for array-objects like SUMPRODUCT.

Hope this helps.

Dave,

I really dont get what youre saying. Could you explain with easier examples?
 

bencar

Banned user
Joined
Jun 8, 2016
Messages
149
Aladin and Dave explained it more thoroughly, but here's another example:

Excel 2010
ABCDEFG
1HXNGEHTZD11
2jimbenalice3030
3WOAbenBAK2345
4FXNBUWEIG71
5jimJXPalice4
6KPUbenRTH83
7JCHLOLZB50
8jimbenalice15
9jimUBJICO85
10jimJCRHIF54

<tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
G2=SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
G3{=SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))}

<tbody>
</tbody>

<tbody>
</tbody>


without CSE only the first number is returned, with CSE you get sum({30,15})

What first number is returned? Using the formula without CSE I get the results in column F. With CSE I get results in column G. Heres what I mean:

HXNGEHTZD11045
jimbenalice303045
WOAbenBAK23015
FXNBUWEIG71015
jimJXPalice4015
KPUbenRTH83015
JCHLOLZB50015
jimbenalice151515
jimUBJICO8500
jimJCRHIF5400

<tbody>
</tbody>




Btwm, how were you able to paste cells from excel in your post??
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,162
Messages
5,594,609
Members
413,917
Latest member
devansh02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top