Cara menggunakan multiply two arrays excel

It takes any number of arrays, multiplies the corresponding cells, and then adds up the outcomes. The arrays have to be the same size for the math to work. Take a look at this quick example to see how SUMPRODUCT works, compared with the basic PRODUCT and SUM functions.

SUMPRODUCT Example

[iframe width=”620″ height=”400″ frameborder=”0″ scrolling=”no” src=”https://skydrive.live.com/embed?cid=56D8503869383D0E&resid=56D8503869383D0E%21120&authkey=ALNKKqCBHC8WZok&em=2&AllowTyping=True&wdDownloadButton=True”]

In the example above, Array 1 (B2:C6) and Array 2 (E2:F6) are just sets of numbers. Using the PRODUCT function, we can multiply the cells together individually and it would look like the PRODUCT table (H2:I6). An example of the formula is in cell I2:

=PRODUCT(C2,F2)

It multiplies 6 by 16 and calculates to 96.

To get the total of these products, we have to sum them. We can sum the columns, as in H9 and H10:

=SUM(H2:H6)
=SUM(I2:I6)

Or we can sum the entire array, as in H11:

=SUM(H2:I6)

SUMPRODUCT lets us save time, economize on space, and get the same result. SUMPRODUCT can multiply two columns together and add them up, as in H12 and I12:

=SUMPRODUCT(B2:B6,E2:E6)
=SUMPRODUCT(C2:C6,F2:F6)

Finally, with its crowning achievement, SUMPRODUCT can multiply two arrays together pair-wise, and then add the results, as in cell H14:

=SUMPRODUCT(B2:C6,E2:F6)

By doing this, SUMPRODUCT can immediately get you the answer you want without all the clutter of the PRODUCT table and the separate SUM formulas. In a crowded spreadsheet, it’s a lifesaver, and you’ll find yourself using it again and again. Check out other functions in the SUM family, including the SUM function and SUMIF function!

Get the latest Excel tips and tricks by joining the newsletter!

Cara menggunakan multiply two arrays excel
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

In this tutorial, I cover how to do a multi-level sorting in Excel. You can watch the video below, or you can read the tutorial below it.

When working with data in Excel, sorting the data is one of the common things you might have to do.

In most of the cases, you need to sort a single column.

But in some cases, there may be a need to sort two columns or more than two columns.

For example, in the below dataset, I want to sort the data by the Region column and then by the Sales Column. This will allow me to see which sales rep has done well in which regions.

Cara menggunakan multiply two arrays excel

While it’s straightforward to sort data by one column in Excel, when it comes to sorting by two columns, you need to take a couple of additional steps.

In this tutorial, I will show you two ways to do a multiple level data sorting in Excel (i.e., sort by two columns)

This Tutorial Covers

Multi-Level Sorting Using Dialog Box

When you sort data using the sort dialog box, you get an option to add multiple levels to it.

Here are the steps to do  multi-level sorting using the dialog box:

  1. Select the entire data set that you want to sort.
  2. Click the Data tab.
  3. Click on the Sort Icon (the one shown below). This will open the Sort dialog box.
    Cara menggunakan multiply two arrays excel
  4. In the Sort Dialogue box, make the following selections
    • Sort by (Column): Region (this is the first level of sorting)
    • Sort On: Values
    • Order: A to Z
    •  If your data has headers, ensure that ‘My data has headers’ option is checked.
      Cara menggunakan multiply two arrays excel
  5. Click on Add Level (this will add another level of sorting options).
    Cara menggunakan multiply two arrays excel
  6. In the second level of sorting, make the following selections:
    • Then by (Column): Sales
    • Sort On: Values
    • Order: Largest to Smallest
      Cara menggunakan multiply two arrays excel
  7. Click OK

The above steps would give you the result as shown below. This sorts the data first by Region and then by Sales column. Note that since it sorts the Region column first when the Sales column is sorted, the Region column remains unchanged.

Cara menggunakan multiply two arrays excel

In this example, I have sorted the data for two columns. You can have more than two-column sorting as well. All you need to do is add these sorting levels and specify the details.

Note: While this method is longer and takes a few more steps (as compared with the multi-sorting method covered next), I recommend using this as it is less confusing.

Multi-Level Sorting Using Sort Icons

Not many people know this way of doing a multiple level data sorting in Excel.

This technique works the same way with a minor difference – you sort the second level first and then move to the first level sorting column.

Here are the steps to do it:

  1. Select the column that you want to be sorted last (in this case, select the Sales data first – C1:C13).
  2. Click on the Data tab.
  3. In the Sort and Filter group, click on the Z to A sorting icon. This will sort the sales data from largest to smallest.
    Cara menggunakan multiply two arrays excel
  4. The above step would make a Sort Warning dialog box pop-up. Make sure ‘Expand the selection’ is selected. This makes sure the entire dataset is sorted, and not just data in the Sales column.
    Cara menggunakan multiply two arrays excel
  5. Click Sort.
  6. Select the Region column.
  7. In the Data tab, click on the A to Z sort icon.
    Cara menggunakan multiply two arrays excel
  8. In the Sort Warning dialog box pop-up, make sure ‘Expand the selection’ is selected.
  9. Click Sort.

The above steps would sort the data just like it did in the first method.

While this method works fine, I recommend using the sort dialog bo method.

Sort dialog box makes it less error-prone (as you can see which levels getting sorted in which order).

Also, there are more ways to sort data with the dialog box. For example, you can sort a column based on the cell/font color and you can also use your own custom sorting criteria.

You May Also Like the Following Excel Tutorials:

  • Sort Data using Custom Lists
  • Automatically Sort Data in Alphabetical Oder using Excel Formulas.
  • How to Sort Data in Excel using VBA.
  • How to do a Multiple Level Data Sorting in Excel
  • Sort Worksheets in Excel using VBA (alphabetically)
  • How to Sort by the Last Name in Excel
  • How to Flip Data in Excel
  • How to Undo Sort in Excel

Cara menggunakan multiply two arrays excel

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Name

Email

YES - SEND ME THE EBOOK

4 thoughts on “How to do a Multiple Level Data Sorting in Excel”

  1. Jame

    Thanks

  2. Aleks Sitar

    Great explanation, everything clear , there is no problem anymore. A real professional at work.Thanks 🙂