Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMIF with broken range
I am trying to get three different sums from the same range:
1) All positive numbers 2) All negative numbers 3) The total (which I can get fine) I tried this all kinds of ways, but the method below works for the negative numbers, but give me an obscenely high (and incorrect) number for the positive numbers: Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with broken range
"Wesler" wrote:
I am trying to get three different sums from the same range: 1) All positive numbers 2) All negative numbers 3) The total (which I can get fine) I tried this all kinds of ways, but the method below works for the negative numbers, but give me an obscenely high (and incorrect) number for the positive numbers: Any ideas? Your example did not come through my newsreader, at least. Not sure how "broken" your ranges are. Post your SUM formula (#3). Copy from the Excel Formula Bar and paste into your posted response. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with broken range
PS.... I wrote:
"Wesler" wrote: I am trying to get three different sums from the same range: 1) All positive numbers 2) All negative numbers 3) The total (which I can get fine) I tried this all kinds of ways, but the method below works for the negative numbers, but give me an obscenely high (and incorrect) number for the positive numbers: Any ideas? I cannot imagine how you can get a "method [that] works for the negative numbers", but it does not work for the positive numbers. I suspect you are misinterpreting your results for the negative numbers. Otherwise, simply reverse the direction of your conditional expression. If you have "<0", use "0". |
#4
|
|||
|
|||
Quote:
=SUMIF((F13),(K13),(P13),(U13),(Z13),(F28),(K28),( P28),(U28),(Z28),(K43),...,(Z193))"<0") |
#5
|
|||
|
|||
The above is not the function as I have. I kept getting an error trying to put it in. Replace the commas with colons and it works for the negative numbers anyway. Also, I truncated the formula, but I think you can get the gist of ti from that.
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with broken range
"Wesler" wrote:
Here's the function to get the negative numbers, which works. [....] =SUMIF((F13),(K13),(P13),(U13),(Z13),(F28),(K28),( P28),(U28),(Z28),(K43),...,(Z193))"<0") Sorry, but I do not believe you. What you posted is not syntactically correct; of course, I removed ",...,(Z193)". If you have a syntax question, you must show us the exact syntax by copying from the Formula Bar and pasting into your posting, as I stated in my first response in this thread. Obviously you did not do that. Better still, upload an example Excel file (devoid of any private data) that demonstrates the problem to a file-sharing website, and post the "shared", "public" or "view-only" link (aka URL; http://...) in a response here. The following is a list of some free file-sharing websites; or use your own. Box.Net: http://www.box.net/files Windows Live Skydrive: http://skydrive.live.com MediaFi http://www.mediafire.com FileFactory: http://www.filefactory.com FileSavr: http://www.filesavr.com RapidSha http://www.rapidshare.com The example Excel file should have the formula that works for your. In another cell, you should also include the formula you would like to make work as text. For the latter, either set the cell format to Text before typing the intended formula, or type an apostrophe before the initial equal sign ("=") in the intended formula. Don't forget to tell us where those cells a the one with the formula that works, and the one with intended formula as text. |
#7
|
|||
|
|||
Quote:
I also don't know how this formula worked for you as this is not the correct form of the SUMIF formula. If your data lies in the range F13:Z193, then try using this SUMIF(F13:Z193,"<0") this sums all the numbers whose value <0. Substitute <0 with 0 to get the sum of positive values. Should work. |
#8
|
|||
|
|||
Quote:
https://www.box.com/s/f338a6b474ebb6e761cb The formulae in question lie beside the first week's calendar Works for negative, doesn't for positive. It's odd. |
#9
|
|||
|
|||
Quote:
As a quickfix, this would work for the full range (a bit messy and I'm sure there are better ways but need to think about it) =SUMIF(B4:Z209,"0") - SUM(B17:Z17,B32:Z32,B47:Z47,B62:Z62,B77:Z77,B92:Z9 2,B107:Z107,B122:Z122,B137:Z137,B152:Z152,B167:Z16 7,B182:Z182,B197:Z197) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with broken range
"Wesler" wrote:
I've posted the a copy of the file: https://www.box.com/s/f338a6b474ebb6e761cb The formulae in question lie beside the first week's calendar Works for negative, doesn't for positive. It's odd. Great! That explains a lot! First, your syntax uses colon separators, not comma separators at you posted previously, at least in my Excel (US English). AFAIK, that should be the same in all regional Excels. I thought it is only the use of comma and semicolon that differ. In any case, the point is: Excel is taking your somewhat nonsensical syntax and trying to make some sense of it. If you used the Evaluate Formula tool, you would see that: =SUMIF((F13):(K13):(P13):(U13):(Z13):[...]:(P193):(U193):(Z193),"<0") is interpreted as =SUMIF($F$13:$Z$193,"<0") It is important for you to understand that (F13):(K13):(P13):(U13):(Z13):[...]:(P193):(U193):(Z193) is __not__ an intentional way to specify a range, "broken" (sparse) or otherwise. The parentheses are redundant and useless. But even F13:K13:P13:U13:etc works only as an accident of implementation. See the "operator" help page. The colon is a "Range operator, which produces one reference to all the cells between two references, including the two references (B5:B15)". Excel has generalized that to behave like any other operator: a sequence of pairwise "operations". Thus, it always produces a continuous ("unbroken") range composed of the first and last cell references. ----- Second, that correctly sums the negative values only coincidentally because within the range F13:Z193, the only negative values are indeed the ones that you want to sum. In contrast, =SUMIF($F$13:$Z$193,"0") does not sum correctly because within the range F13:Z193, you have other non-negative values that you do not intend to include in the sum, namely the dates in rows 17, 32, etc. (But both sums might have been incorrect if you had legitimate negative or positive values in the "vac" column, for example.) If we can rely on the "X" strings in column A, the following is the correct way for you to sum both positive and negative amounts. =SUMPRODUCT((LEFT(A4:A194,1)="x")*(B3:Z3="xc")*(B4 :Z1940),B4:Z194) =SUMPRODUCT((LEFT(A4:A194,1)="x")*(B3:Z3="xc")*(B4 :Z194<0),B4:Z194) I have expanded the rows and columns to be all-inclusive of the Oct through Dec dates, and to make it easy for you to modify the formulas to sum other types of columns, e.g. "vac". If we cannot rely on the "X" strings in column A, I would suggest that you insert a "helper" column with "X" strings. The helper column can be hidden. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with broken range
PS.... I wrote:
If we can rely on the "X" strings in column A, the following is the correct way for you to sum both positive and negative amounts. =SUMPRODUCT((LEFT(A4:A194,1)="x")*(B3:Z3="xc")*(B4 :Z1940),B4:Z194) =SUMPRODUCT((LEFT(A4:A194,1)="x")*(B3:Z3="xc")*(B4 :Z194<0),B4:Z194) [....] If we cannot rely on the "X" strings in column A, I would suggest that you insert a "helper" column with "X" strings. The helper column can be hidden. It just occurred to me that the "X" strings probably represent people's names, which you prudently edited out of the example file. But a helper column with "X" strings is not needed. The following should produce the same results: =SUMPRODUCT((A4:A194<"")*(B3:Z3="xc")*(B4:Z1940) ,B4:Z194) =SUMPRODUCT((A4:A194<"")*(B3:Z3="xc")*(B4:Z194<0) ,B4:Z194) |
#12
|
|||
|
|||
Quote:
I finally got what I wanted by doing this: =SUMPRODUCT((A4:A194="X10")*(B3:Z3="xc")*(B4:Z194 0),B4:Z194) Having a formula for each name: X1, X2 &c. But the thing is I have quarterly calendars which are set up exactly the same, whatever cell contains a name or label on one, does so in every other one, but it only works in the first sheet. This shouldn't be, should it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
Dynamic Range broken | Excel Programming | |||
Broken: Set Cells in Range to 0 if blank | Excel Programming | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Deleting broken range names | Excel Programming |