Working with Arrays

Problem: I have a table of quantities and unit prices for 3 products. I need to get the total sales value of all 3 products in one column without the hassle of having multiple columns.

Solution: For that purpose, you can use Arrays. Arrays is a method of managing formulas on Excel in a way that:

1.       Protects formulas in adjacent cells from accidental modification.

2.       Ensures uniform and consistent formula throughout the array of cells.

3.       Performs complex calculations not otherwise doable without Arrays.

To create an Array, all you have to do is to select the cells where you need to have the formula populated, enter the formula in the first cell, then press CTRL + SHIFT + ENTER, as opposed to pressing ENTER, to identify it as an Array.

Therefore, to resolve the current problem, place the selection over the yellow cell, and expand the selection to the end of the table, then enter the following formula assuming that the figures in the table start from C11 and end on H22:

=C11:C22*D11:D22+E11:E22*F11:F22+G11:G22*H11:H22

	Sales by Product
Month	A		B		C		Total Sales
	QTY	Unit Price	QTY	Unit Price	QTY	Unit Price
Jan	656	74	274	29	770	55	98,536
Feb	434	76	638	29	716	52	88,733
Mar	264	70	574	25	768	53	73,286
Apr	676	65	193	27	560	52	78,291
May	583	69	37	25	882	50	85,259
Jun	607	68	442	30	585	49	83,176
Jul	628	66	988	31	104	53	77,569
Aug	139	66	47	28	39	57	12,669
Sep	615	67	410	22	113	50	55,925
Oct	883	71	748	24	748	59	124,779
Nov	626	70	738	25	84	52	66,665
Dec	519	77	227	28	998	50	96,369

After entering the formula, press CTRL + SHIFT + ENTER. You will notice that the formula has become within brackets as follows:

={C11:C22*D11:D22+E11:E22*F11:F22+G11:G22*H11:H22}

If you edit any of the cells within the formula you will notice the following:

1.       The brackets disappear as long as you are in edit mode.

2.       If you try to edit one or more cells (but not the entire array), you will receive the following error message:

 

     As the cells are protected within the array. You either keep the array as is or delete it as a whole. However, if you want to edit the array formula, you should select the entire array, do the editing then press CTRL + SHIFT + ENTER.

Note that if you try to press ENTER only, you may receive an error like #VALUE! since it is an array formula.