SUMIF with broken range

[Wesler]

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?




--
Wesler

[joeu2004]

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.

[joeu2004]

PS.... I wrote:
> "Wesler" <Wesler....@excelbanter.com> 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".

[Wesler]

'joeu2004[_2_ Wrote:
> ;1604720']PS.... I wrote:-
> > "Wesler" <Wesler....@excelbanter.com> 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".

Here's the function to get the negative numbers, which works. When I
inverse the operator, it doesn't. Makes no sense I know, but such is
Excel or my using of it.

=SUMIF((F13),(K13),(P13),(U13),(Z13),(F28),(K28),(P28),(U28),(Z28),(K43),...,(Z193))"<0")




--
Wesler

[joeu2004]

"Wesler" <Wesler....@excelbanter.com> 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
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: 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 are: the one with the formula
that works, and the one with intended formula as text.

[Wesler]

SingleMalt;1604792 Wrote:
> 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.

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.




--
Wesler

[joeu2004]

"Wesler" <Wesler....@excelbanter.com> 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:Z194>0),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.

[joeu2004]

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:Z194>0),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:Z194>0),B4:Z194)
=SUMPRODUCT((A4:A194<>"")*(B3:Z3="xc")*(B4:Z194<0),B4:Z194)

[Wesler]

'joeu2004[_2_ Wrote:
> ;1604867']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:Z194>0),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:Z194>0),B4:Z194)
> =SUMPRODUCT((A4:A194<>"")*(B3:Z3="xc")*(B4:Z194<0),B4:Z194)

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?




--
Wesler