Excel Basic Array learning (Part-2)

Simple Example :-

Below is the Example of part of monthly home expenditure in Rupees.

In column Total Expenses i.e. column D , The formula for D2 is =B2*C2, likewise for D3 it’s =B3*C3 and so on.  We can reduced the formula in column E as by array. For that you have to select the range E2:E8, and in formula bar write =B2:B8*C2:C8 and press ctrl+shift+enter  for curly brackets which appears as {=B2:B8*C2:C8} .

Again, below Excel screenshot is the same as above, just extended the SUM function with or without array.

Usually, we calculated D column which I showed above. Then, with SUM function we used =SUM(D2:D8).

Let’s consider how array reduced column. Now we don’t need extra column like column D and with single formula we are done. Formula is {=SUM(B2:B8*C2:C8) }. Don’t forget that curly brackets are from pressing ctrl+shift+enter.

 

 Note:- In this case, we can also use SUMPRODUCT function without using Array.

i.e  =SUMPRODUCT(B2:B8,C2:C8)