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

Discuss the spreadsheet application
Post Reply
cliffb
Posts: 3
Joined: Fri Feb 14, 2020 6:10 am

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

Post by cliffb »

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!

Here's essentially the spreadsheet:

[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
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post by Zizi64 »

Always upload a sample file to demonstrate what sou had tried to do.
Here is my sample file:
SUMIF.ods
(15.58 KiB) Downloaded 130 times
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

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

Post by robleyd »

Try this:

Code: Select all

=SUMIF(A2:A10;">"&TODAY()-30;B2:B10)
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
cliffb
Posts: 3
Joined: Fri Feb 14, 2020 6:10 am

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

Post by cliffb »

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
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post by Zizi64 »

The

Code: Select all

" >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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

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

Post by robleyd »

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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
cliffb
Posts: 3
Joined: Fri Feb 14, 2020 6:10 am

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

Post by cliffb »

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
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

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

Post by robleyd »

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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Post Reply