Summary of data from multiple Sheets in Excel

The following article details how to aggregate data from multiple sheets in Excel.

Example: Summarizing the 3-quarter revenue of a software store in 2015. The 3-quarter data in the order in Sheet is Quarter 1, Quarter 2, and Quarter 3. Sum the revenue of products into the Total Sheet well suited.

How to do it: Use the Vlookup function to aggregate data from sheets. Because the items sold in each quarter are different, use the IF and ISNA functionsto check. If the name of the product exists in the first quarter, then proceed to add revenue. This helps you avoid N / a errors.

Step 1: Create a sheet named General -> Create a list of all products sold in the 3 Quarter.

Summary of data from multiple Sheets in Excel Picture 1

Step 2: In cell E4 enter the following command:

Summary of data from multiple Sheets in Excel Picture 2

Step 3: Enter the table value containing the search value of the Vlookup function in the first quarter.

- Move to Sheet Rule 1 -> select the data area containing the item name and data to aggregate.

Summary of data from multiple Sheets in Excel Picture 3

- Press Enter -> dialog box appears -> OK to return to the Summary Sheet . Result:

Summary of data from multiple Sheets in Excel Picture 4

Note: Because the lookup table is on a different sheet, the sheet name must be before the address of the data cell.

Step 4: Convert the address of a cell into an absolute address.

- Select the address -> F4 -> Address absolute conversion.

Summary of data from multiple Sheets in Excel Picture 5

Step 5: If Q1 contains the value to be searched -> use the Vlookup function to get the revenue of the value to be searched in Q1 -> with the following statement:

Summary of data from multiple Sheets in Excel Picture 6

Step 6: Do the same thing for Quarter 2 and 3 and calculate the total for 3 quarters -> Summary statement:

Summary of data from multiple Sheets in Excel Picture 7

How to enter the command:

- Each quarter will have 1 duplicate order, you should use the Copy command .

IF (ISNA (VLOOKUP (D5, 'Rule 1'! $ D $ 4: $ E $ 8.2,0)), 0, (VLOOKUP (D5, 'Rule 1'! $ D $ 4: $ E $ 8.2,0) )) : Use the ISNA function to check if a return value is the value of the item.

Step 7: After entering the command you press Enter -> Copy the formula for the remaining items -> Results:

Summary of data from multiple Sheets in Excel Picture 8

Good luck!