Does SUMIFS work in the latest version?

[digipro00]

I LOVE calc, and hate Excel. However I have a project now that needs to use SUMIFS and COUNTIFS. The following formula works perfect in Excel, but gives the result of “0” in Calc. (I have formula syntax set to Excel A1 for this formula, but I have also tried it in Calc A1)

=SUMIFS(I:I,D:D,“2014-04*”,F:F,“2014-05*”,C:C,{“paid”,“partially*”})

I cant get it to work at all. Is it because SUMIFS does not work, or am I doing something wrong? Whats odd to me is that SUMIFS shows as a formula option in Calc, but doesnt come up in Calc wikis, or even that often in discussions. Thanks guys!

[Rugslug]

I have a SUMIFS function working in 4.2.4 and 4.3.0. Although well versed on SUMIFS, you could check the I:I that maybe they need to be I1:I30 (specify rows). Also the date format may be different in LO from Excel or maybe your function is looking for text of “2014-04” etc. You might want to build your function one check at a time to see where the fault occurs.

[Rugslug]

A period will be required for the wildcard, that is, “partially.*” I also haven’t had any success with two criteria within the { } brackets.

[Lupp]

Not knowing recent Excel I have to ask: Shall using an inline array like {“paid”,“partially*”} as a criterion offer an alternative (ORing) or shall it be an abbreviation for imposing TWO criteria on the data (ANDing)? As I don’t like bothering about such questions I wouldn’t rely on SUMIFS() in such a “complicated” case but use helpers for getting it CLEAR and a simple SUMIF() for the rest.

If you insist on SUMIFS() just for ANDing a few criteria: answer my question, please. I will try then.

[Lupp]

(I did it already, in fact - but don’t like the result.) Best: YOU attach a file containing an example.

[Pedro1]

The problem is probably not related to SUMIFS but the way Excel selects a whole column. Unfortunately Calc does not accept I:I (actually, you can’t even select a column as target in a Calc formula…)

You need to modify to I1:I1048576 (which is a bit absurd because the number of rows varies with file format and with program versions…).

[erAck]

Just to mention that as of release 5.0 Calc also accepts I:I as an entire column range.