Sumif(s) and date in criteria

Docs
, ,
1

Hi there,

I’m trying to port my Google Docs spreadsheets to onlyOffice and I am struggling a bit with some functions that need rewriting.
I hope you guys & girls can help me with this.

As an example, I have a sheet with two columns, where column A contains dates like 2023-05-04, 2024-06-07, and so forth, and column B contains amounts. I want to sum up the amounts from column B, when column A contains a specific year.

In Google Docs, we got the QUERY function, which takes a range of data and holds a query against it.
Like so: QUERY(A1:B10;“select Sum(B) where A contains ‘2025’ label sum(B) ‘’”)

In onlyOffice we got the SUMIF function.
I would rewrite the QUERY function like this:
SUMIF(A1:A10;“*2025”;B1:B10)

But that doesn’t work (it produces 0) and I figure it’s because of the fact that column A is a date, which is formatted in 2025-01-01 but really contains a very different value, something like 45658.

I have tried things like SUMIF(A1:A10;“>DATE(2025,1,1)”;B1:B10) or
SUMIF(A1:A10;“>”&DATE(2025,1,1)";B1:B10)

But those tricks don’t work.

Does anybody have a clue for me? :slight_smile:

2

Oh by the way, forgot to mention I’m using onlyOffice online, version 7.2.2.56

3

Hi @DeejaySane,
Please note that current Document Server version is 9.0 and version 7.2.2 was released almost 3 years ago. Please update your Document Server and check if the issue is reproduced. If it is, provide a sample file the issue can be reproduced with.

4

Hi there Carl, thanks for your message.
Yes, I thought as much, the server is not mine I’m afraid but I will ask the administrators to update :slight_smile:

5

You can test the latest version of the editors here:

6

Hi Carl,

Thanks for the link.
After testing my data there, I think I found the following:

When I enter a date in a cell and the sheet automatically formats it as “Date”, it converts the date to a number (1/7/2025 for example will be represented by the number 45664), in which case the SUMIF will find nothing (0).

But when the cell doesn’t get formatted as “Date”, like when I manually set the cell to “Text”, SUMIF will work :slight_smile:

The strangest part however is that when I manually set the cell to “Date” afterwards, the date is not converted to a number and SUMIF will work as expected :confused:

Things like SUMIF(A1:A10;“>DATE(2025,1,1)”;B1:B10) or
SUMIF(A1:A10;“>”&DATE(2025,1,1)";B1:B10) or something like SUMIF(A1:A10;YEAR(2025);B1:B10) don’t work.

The search continues :slight_smile:

7

Hello @DeejaySane

If your column A contains data in Date format and column B contains data in Number format, then you can easily use formula:

=SUMIF(A1:A4,">1/1/2025",B1:B4)

Here is demo with simple values for the reference:
image

There is actually no need to convert dates into numbers and using DATE or YEAR, as they mess up with final calculation. Simply relying on exact dates will do the trick.

8

Hi @Constantine, super, thanks for your reply!
Much appreciated.
I will try this out.
The converting to a number happens automagically for some reason…

Do you by any chance know if something like “between” is possible?
So something like

=SUMIF(A1:A4,">1/1/2025 and <1/12026",B1:B4)
9

You can always disable it. Anyway, date is counted as a number internally, so there should be no issue with that.

It is, but not with SUMIF formula, because it takes only one criteria, but with SUMIFS, for instance, like that:

=SUMIFS(B1:B6,A1:A6,"<1/1/2024",A1:A6,">1/1/2023")

image
image884×348 10.3 KB

10

Hi @Constantine
Thanks for all your help! Really appreciate it.
Going to try this out!

1 Like
11

Hi @Constantine
Yes it works! Thanks.
I think it is a bit strange that I have to enter the criteria-range twice, but hey, I can do that :wink:

1 Like
12

I am glad to know. Basically, SUMIFS works with multiple conditions, its the only way to achieve scenario like “between this and that”, because SUMIF works with only one condition.

Frankly, SUMIFS is a very flexible formula.

I suppose the topic can be closed. If you do not feel like it, please PM, I will re-open it.

1 Like