Data Analysis Using Excel Array Formulas

Excel array formulas are useful and powerful formulas to summarize your spreadsheet data. You have the option of using a single or multiple criteria to summarize your worksheet data and also create a customized report.
How to implement array formulas in Excel

Enter your data and organize it.
Next select appropriate ranges and name them as shown in the training video. Named ranges make the use of array formulas easier.
Enter your formuala.
Now press 'CTRL'+'SHIFT'+'ENTER' keys simultaneously to display the result of the formula.
In case you forget to press the keys and press only enter, you will see the '#value' error.
You can use functions like sum, average, max, min, etc. in your array formulas.

We have also demonstrated that if you are not comfortable with array formulas you can use the database functions to do similar analysis.

Some knowledge about how the array formulas work in the background

Place your cursor in cell E12 where the value '66' is displayed. Press 'F2' to view the formula. In the formula bar above now select 'product="Shirts" ' and press 'F9'. You'll notice 'FALSE' and 'TRUE' displayed. Wherever the value 'shirts' is available, 'TRUE' is displayed otherwise 'FALSE' is displayed.

Now select 'quantity' and press 'F9' on the keboard to display the 'quantity' of shirts.

Only where the 'shirts' is 'TRUE' the 'quantity' values are added and you get the result '66'.
In this last image below you select both 'product="Shirts" ' and 'quantity' and press 'F9' to see all the background data.