can u send the working file.

Yeah Tanu pls specify your problem more precisely or else upload the file ( remove names for secrecy reasons) here so that i can see excatly what it is actually and help you in better way......

this is consolidate file

this is main data file. when u'll open consolidate file and will use filter function, u'll find error.

please check and guide me where i am at mistake

Dear Tanu,

You have changed the name of file that you have uploaded.

after cheching the syntex it is found that you have used wrong syntex.

The correct syntex is :

SUMIF(range,criteria,sum_range)

Range    is the range of cells you want evaluated.

Criteria    is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples".

Sum_range    are the actual cells to sum.

please go through the attached file. if any query feel free to contact.

Regards,

Manoj Garg

email id: datacure @ gmail.com

Mob: +91-9927199217

thanx Manoj to guide, but first i request to send above file in excel format, so that i can see it and as u wrote to use Sumif instead of sumifs, i used that also but in that formula all separate value were not displaying even. so i used sumifs formula

As desired, excel files are uploaded. rename these files to there original names:

consolidated new .xls

data.xls.

As desired, excel files are uploaded. rename these files to there original names:

consolidated new .xls

data.xls.

As desired, excel files are uploaded. rename these files to there original names:

data.xls.

Dear Tanu,

you are viewing the file created in excel 2007 in any lower version of excel. Because ithe function sumifs() is not in lower version of excel 2007

SUMIFS function **(available in excel 2007)

Show All

Hide All

This article describes the formula syntax and usage of the SUMIFS function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Descripttion

Adds the cells in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) that meet multiple criteria. For example, if you want to sum the numbers in the range A1:A20 only if the corresponding numbers in B1:B20 are greater than zero (0) and the corresponding numbers in C1:C20 are less than 10, you can use the following formula:

=SUMIFS(A1:A20, B1:B20, ">0", C1:C20, "<10")

 Important   The order of arguments differ between the SUMIFS and SUMIF functions. In particular, the sum_range argument is the first argument in SUMIFS, but it is the third argument in SUMIF. If you are copying and editing these similar functions, make sure you put the arguments in the correct order.

Syntax

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,
         criteria2], …)

The SUMIFS function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• sum_range    Required. One or more cells to sum, including numbers or names, ranges, or cell references (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) that contain numbers. Blank and text values are ignored.
• criteria_range1    Required. The first range in which to evaluate the associated criteria.
• criteria1    Required. The criteria in the form of a number, expression, cell reference, or text that define which cells in the criteria_range1 argument will be added. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32."
• criteria_range2, criteria2, …    Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

I think this will work.

Regards,

Manoj Garg

Dear Tanu,

If you are using external links, and if all the files which are linked are not open. The sumif function will return an error.  I will tell you another formula later, which will work on external link. Till, either you open all the linked files or use the formula in same file.

Regards,

Manoj Garg