How to SUM Values from Multiple Sheets in Active Workbook
The sum of values from multiple worksheets in a particular condition in Excel
In this tutorial I will show how we can SUM a certain value for a certain condition (which is repeated on the other worksheets) from multiple worksheets in a cumulative worksheet. The situation in the picture below you notice all the worksheets. Introduces example is based on twelve months in the current year.
- Worksheet 'Cumulative' is a worksheet where we show all the values for a particular condition, these values are copied to other worksheets depending on the name of the worksheet (the name of the month in the year)
- Worksheets that are named the 'Name of the Month' in the current Year (I display the names of only three months, JAN, FEB, MAR)
- Worksheet 'DefinedNames' where we have a list of conditions. In this Excel Example, the list contains the names of persons and can be any data type.
In the picture above you can see:
Column 'A' contains the names of people (or other information). This data is Unique to each individual worksheet months of the year. These figures can be placed over the Data Validation drop down menu or directly by entering the names (data)
Also you notice that for each month there are three columns. These data automatically using the formula is copied from the worksheets that have names of months in a year or so to return the result of a condition set out in column 'A'.
- The first column Value1
- The second column Value2
- The third column Value1/Value2
Worksheets with the names of twelve months in the current year have a short name (JAN, FEB, MAR ...) for simplicity and also because the named of the range data that I have named the full name of the month in the year (january, february, march ....)
Otherwise, I suggest that you always name the cell ranges for easy creation formula.
So here it is important to note the named range of data '$A2$:$D$20' where on each worksheet contains the names of persons involved in the calculations for a given month. If necessary, you can expand this range.
On this worksheet, you notice that I have defined a range of cells '$A$2:$A$27'.
I named a range of cells called "names". This worksheet is used for that in him we can add the names of persons because of each month do not have all the people on the list ie in the calculation.
How to Add Value from Multiple Worksheets for a Specific Name in Excel
Let's go back to the first image of this tutorial on the worksheet "Cumulative". For each month you need to copy data from a specified column from a specific worksheet for requirement that appears in column 'A'.
The formula in column B (B2 cell) - this formula should return the result from worksheet 'JAN' for the condition from 'A' column, ie. Cell 'A2'. If you look at the requirement (name) in the 'A' column on the worksheet 'JAN' You will note that this value is 301, while the value of 201 in the worksheet 'FEB' on the worksheet 'MAR' has no value because there is no name of the person 'A'
The formula in Excel cell 'B2' is as follows:
Here I used the Excel COLUMN function I nested in the INDIRECT functionfunction, and we have installed in the respective VLOOKUP function and all this again installed in IFERROR function to avoid displaying error if data are not available for the condition from 'A' column.
Excel COLUMN function replaces the third argument in a VLOOKUP functionfunction (this argument is normally the column number from which return result).
Excel functions Indirect replaces the second argument which is the range of data or 'array_table' who searching. This Excel INDIRECT functions, using the name of the month from the cell 'B1' and it is 'January'. This is identical to the name of the month named range of cells on the worksheet 'JAN' you can see in the picture above for a specific worksheet.
Excel VLOOKUP function, returns the result in column no. 2 on the worksheet 'JAN' for the condition of the cell 'A2'.
Excel IFERROR functions. In the event that there were no data, for the condition in column 'A' on the worksheet 'JAN'. VLOOKUP formula would return the error "#N/A'. To avoid displaying these errors whole VLOOKUP function implant in IFERROR function.
The formula in cell Excel 'C2' is the following:
This formula is identical to the previous except that the result should be returned in column number 3 which we defined Excel COLUMN function (C1). Functioning and explanation of this formula, as in all previous formula above.
The formula in Excel cell 'D2' is as follows:
- In the cell 'D2' have a formula which has two conditions, but is related to cell "B2" and "C2". If both cells filled then the formula to execute an operation of sharing 'B2/C2'.
- In how many there is no data, in these cells the formula returns an error '#VALUE!', Displaying these errors we avoid with the IFERROR function.
- If there is no data in the cell 'B2' formula returns an empty cell as zero, we can not division with other numbers and displaying these errors with the avoid IFERROR function.
- If there is no data in the cell 'C2' formula returns an error '#DIV/0!' because the number can not divide by zero (0) and display these errors with the avoid IFERROR function.
- Use the Excel ROUND function in which we ensconced IF, the value is rounded to three decimal digits. Of course this is not necessary if you do not want.
And finally, other formula for the month of FEBRUARY.
In cell 'E2' formula is:
In cell 'F2' formula is:
In cell 'G2' formula is:
The appearance of formulas in columns for the month of MARCH I leave you to explore.