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 formula.
- 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 backgroundArray Formulas – Sum Product
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.Array Formulas – Sum Blue Shirts
Now select ‘quantity’ and press ‘F9’ on the keboard to display the ‘quantity’ of shirts.
Array Formulas – Sum 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.