Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

The sum of values from multiple worksheets for a particular condition in Excel

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 called 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 example, the list contains the names of persons and can be any data type.
How to sum to a specific name or condition all values with multiple worksheets

Worksheet Cumulative


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

Worksheet JAN

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 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.

How to name a range of cells in Excel

Worksheet FEB

February Defining called range of data on a worksheet in Excel

Worksheet MAR

define the name of the range of cells on the sheet in Excel

Worksheet DefineNames

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 define a range of cells in Excel column

How to add value from multiple worksheets for a specific name in Excel 2013

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) - 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:

=IFERROR(VLOOKUP($A2;INDIRECT($B$1);COLUMN($B1);FALSE);"")

Here I used the Excel function COLUMN() I nested in the function INDIRECT, and we have installed in the respective function VLOOKUP and all this again installed in IFERROR function to avoid displaying error if data are not available for the condition from 'A' column.

Excel function Column replaces the third argument in a function VLOOKUP (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 functions Indirect, 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 functions IFERROR. 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:

=IFERROR(VLOOKUP($A2;INDIRECT($B$1);COLUMN($C1);FALSE);"")

This formula is identical to the previous except that the result should be returned in column number 3 which we defined Excel function Column (C1). Functioning and explanation of this formula, as in all previous formula above.

The formula in Excel cell 'D2' is as follows:

=IFERROR(ROUND(IF(OR(IF(AND(B2="";C2="");"";B2/C2)="";IF(AND(B2>0;C2>0);B2/C2;"")="");"";B2/C2);3);"")

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 function ROUND in which we ensconced IF, the value is rounded to three decimal digits. Of course this is not necessary if you do not want.

How to sum all values from multiple worksheets for certain criteria

And finally, other formula for the month of FEBRUARY.

In cell E2 formula is:

=IFERROR(VLOOKUP($A2;INDIRECT($E$1);COLUMN($B1);FALSE);"")

In cell F2 formula is:

=IFERROR(VLOOKUP($A2;INDIRECT($E$1);COLUMN($C1);FALSE);"")

In cell G2 formula is:

=IFERROR(ROUND(IF(OR(IF(AND(E2="";F2="");"";E2/F2)="";IF(AND(E2>0;F2>0);E2/F2;"")="");"";E2/F2);3);"")

The appearance of formulas in columns for the month of MARCH I leave you to explore.