Pivot table and array formula to sum total Q'ty

Pivot Table in excel is quite useful to show a table in various views as we want. It can give us total quantity by each product type. However, if we would like to get only the summation of Q'ty for certain product type, using of array formula will give us the result without creating any pivot table.

To create a Pivot Table, select the database table > INSERT > PivotTable

Choose the fields and we'll get the summation of Q'ty by Type.

However, if we would like to get the total q'ty of desired inventory type without creating the pivot table first, we can do that using the array formula.

Write the array formula as follows.

=SUM((Array1)*(Array2)) -- Just for explanation

=SUM((D4:D13=H11)*(E4:E13)) -- Real formula

But don't just press ENTER since it will be only a normal formula which will have an error. We have to press CTRL+SHIFT+ENTER and we will see the braces { and } at the beginning and the end of the formula.
We will see like this: {=SUM((D4:D13=H11)*(E4:E13))}

Please note that we can't is just type { and } in the formula!!

For this example, Array1 contains 0 and 1 according to the comparison between Type and Desired Type (cell H11)

Watch the video to see how we do all steps.

How to draw a circle in Excel

Microsoft Excel has various graphing options. The x-y chart allows us to draw any points or lines and it will automatically connects those points  to form a curve. We can use x-y chart to create a circle which can change its radius and center point.

To draw a circle in excel, first we have to prepare a table for x and y from angle (f) = 0 to f =360 deg. However, we have to modify the formula since Microsoft Excel uses radian instead of degree to calculate value of sine and cosine.

To convert angle from degree to radian, we use

Angle (rad) = Angle (deg.) x pi/180

Value of pi in excel can be referred as PI()
Therefore the formula of x and y become...

x= rcos(fxPI()/180)
y= rsin(fxPI()/180)

To offset the center point, the final formula becomes

x= rcos(fxPI()/180)+xc
y= rsin(fxPI()/180)+yc

In excel, after we write a formula in one cell, when dragging to other cells, it will change the cell reference. We can avoid this if we would like to let excel point to only desired cell by changing the reference with $ symbol by just repeatedly pressing F4.

Example:
Formula of x: write  =$D$3*COS($B8*PI()/180)+$D$4 instead of writing =D3*COS(B8*PI()/180)+D4

Using $D$3 will always refer to cell D3 which is the value of a radius.
If we write D3, after dragging the formula to other cells, the value of a radius will change to cell D4, D5, D6, ... which is wrong.