Wesler said:
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
13: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.
