## [Solved] Help me fix "=+SUMIF(... ;" >TODAY()-30" ;...)"

### [Solved] Help me fix "=+SUMIF(... ;" >TODAY()-30" ;...)"

I am trying to sum a column of quantities over the last 30 days. Col A is a column of dates. Col B is the quantity of something for that date. The goal is to produce a sum of all quantities over only the last 30 days.

I've tried a number of variations on the cell formula in the subject line. The problem seems to be the test itself. Specifically, if I hardcode a date in number format, it works. But, when I introduce the TODAY() function, it breaks and always produces 0. I've tried "<TODAY()", referencing a cell that contains the current date returned by +TODAY(), an actual date entry, etc. The only thing that seems to work is to put in a 5 digit number representation of a date, or to reference a cell with a 5 digit number.

Why doesn't it work? Is this a bug?

Or, if I have it wrong, can someone please correct the condition for me, or tell me what I'm doing wrong????
thanks!

[Table=]
This is cell A1 02/13/20 Today's date
TOTAL (30 d): 0 <<-- Formula in B2 is this: =+SUMIF(A4:A10 ;" >TODAY()-30" ; B4:B10 )
Date Quan Comment
01/15/20 1
01/15/20 1
01/26/20 1
01/30/20 1
02/13/20 2

[/Table]
Last edited by cliffb on Fri Feb 14, 2020 9:47 am, edited 1 time in total.
OpenOffice 4.1.7 on Windows 7
cliffb

Posts: 3
Joined: Fri Feb 14, 2020 6:10 am

### Re: Help me fix "=+SUMIF(A8:A50 ;" >TODAY()-30" ; B8:B50 )

Always upload a sample file to demonstrate what sou had tried to do.
Here is my sample file:

SUMIF.ods
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.1; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 8924
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: Help me fix "=+SUMIF(A8:A50 ;" >TODAY()-30" ; B8:B50 )

Try this:
Code: Select all   Expand viewCollapse view
`=SUMIF(A2:A10;">"&TODAY()-30;B2:B10)`
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 3197
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

### Re: Help me fix "=+SUMIF(A8:A50 ;" >TODAY()-30" ; B8:B50 )

Thanks. I hadn't considered moving the function outside of the quotes and didn't see anything in the docs related to this that discuss the use of quotes. Admittedly, my search wouldn't be considered "rigorous".

Thanks again for the help!
OpenOffice 4.1.7 on Windows 7
cliffb

Posts: 3
Joined: Fri Feb 14, 2020 6:10 am

### Re: [SOLVED]Help me fix "=+SUMIF(... ;" >TODAY()-30" ;...)"

The
Code: Select all   Expand viewCollapse view
`" >TODAY()-30"`

string can not work as a TODAY() function, because it is a STRING, but but that is not a command, that is not a working function.
If the TODAY() goes outside the quotes, it will work.
The case of the constant 30 is same. The "30" is a string with zero numeric value, but you need a numeric value: 30.

These are basic things, they are not related to this feature only. Therefore it is not described in the HELP of every functions, what uses a combined (string+numeric value) parameter.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.1; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 8924
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: [SOLVED]Help me fix "=+SUMIF(... ;" >TODAY()-30" ;...)"

The HELP for SUMIF says "See COUNTIF() for some more syntax examples that can be used with SUMIF()." There you'll find an example
=COUNTIF(A1:A10;"<"&B1) - when B1 contains 2006, this returns 6
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 3197
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

### Re: [SOLVED]Help me fix "=+SUMIF(... ;" >TODAY()-30" ;...)"

Exactly. But why is it really necessary to put the > in quotes, and then having to concatenate with the &. That's what threw me. Otherwise, I had the whole thing.

My natural instinct was to do this. +SUMIF(A8:A50 ; >TODAY()-30 ; B8:B50 )
I added the quotes based on an example I found online and it didn't work.
OpenOffice 4.1.7 on Windows 7
cliffb

Posts: 3
Joined: Fri Feb 14, 2020 6:10 am

### Re: [SOLVED]Help me fix "=+SUMIF(... ;" >TODAY()-30" ;...)"

As Ziz64 said, inside quotes the function name, or a cell reference is treated as a string. Outside the quotes it is de-referenced to return the value of the function result or cell content.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 3197
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia