Ask Your Question
0

Does SUMIFS work in the latest version? [closed]

asked 2014-06-09 17:26:22 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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!

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-25 00:16:53.970627

Comments

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 gravatar imageRugslug ( 2014-06-09 18:00:43 +0200 )edit

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

Rugslug gravatar imageRugslug ( 2014-06-09 18:48:58 +0200 )edit

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 gravatar imageLupp ( 2014-06-09 19:36:55 +0200 )edit

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

Lupp gravatar imageLupp ( 2014-06-09 19:39:03 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2014-06-09 17:57:07 +0200

Pedro gravatar image

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

edit flag offensive delete link more

Comments

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

erAck gravatar imageerAck ( 2016-08-29 15:57:53 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-06-09 17:26:22 +0200

Seen: 2,731 times

Last updated: Jun 09 '14