FREE Thai Alphabet Game F for Android

FREE Thai Alphabet Game F for Android
Free app for learning Thai Alphabet on Play Store

Sunday, February 22, 2015

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.

No comments:

Post a Comment