Master TDS & TCS Provisions with Critical Issues. Register Now!

sum if query

Tanu (Accountant) (767 Points)

03 June 2010

i am using sumifs formula which is not working in networking. and also when i use filter option with it all figures converted in error. guide me.

thanx

Reply
Follow
Share

12 Replies

Manoj Garg (Support on Tally Excel Password Recovery etc.) (1987 Points)
Replied 03 June 2010

can u send the working file.

Upgrade to CAclubindia Pro. Subscribe Now!

CA GIRISH (Chartered Accountant) (1796 Points)
Replied 04 June 2010

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

Tanu (Accountant) (767 Points)
Replied 05 June 2010

this is consolidate file

Attached File : 47 consolidated sheet .xls downloaded: 238 times

Tanu (Accountant) (767 Points)
Replied 05 June 2010

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

Attached File : 41 data.xls downloaded: 241 times

Manoj Garg (Support on Tally Excel Password Recovery etc.) (1987 Points)
Replied 05 June 2010

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

Attached File : 14 desktop.zip downloaded: 266 times

Upgrade to CAclubindia Pro. Subscribe Now!

Tanu (Accountant) (767 Points)
Replied 05 June 2010

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

Manoj Garg (Support on Tally Excel Password Recovery etc.) (1987 Points)
Replied 05 June 2010

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

consolidated new .xls

data.xls.

Manoj Garg (Support on Tally Excel Password Recovery etc.) (1987 Points)
Replied 05 June 2010

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

consolidated new .xls

data.xls.

Attached File : 17 consolidated new .xls downloaded: 174 times

Manoj Garg (Support on Tally Excel Password Recovery etc.) (1987 Points)
Replied 05 June 2010

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

data.xls.

Attached File : 53 data.xls downloaded: 268 times

Manoj Garg (Support on Tally Excel Password Recovery etc.) (1987 Points)
Replied 05 June 2010

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)

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

Upgrade to CAclubindia Pro. Subscribe Now!

Tanu (Accountant) (767 Points)
Replied 05 June 2010

again there is same problem. If I close both file and then open consolidate file only and apply filter for getting some particular, values converted in error.

Manoj Garg (Support on Tally Excel Password Recovery etc.) (1987 Points)
Replied 05 June 2010

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