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.
Step 2: In cell E4 enter the following command:
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.
- Press Enter -> dialog box appears -> OK to return to the Summary Sheet . Result:
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.
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:
Step 6: Do the same thing for Quarter 2 and 3 and calculate the total for 3 quarters -> Summary statement:
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:
Good luck!