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

Discuss the spreadsheet application

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

Postby cliffb » Fri Feb 14, 2020 6:33 am

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
cliffb
 
Posts: 3
Joined: Fri Feb 14, 2020 6:10 am

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

Postby Zizi64 » Fri Feb 14, 2020 7:39 am

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

SUMIF.ods
(15.58 KiB) Downloaded 5 times
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.
User avatar
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 )

Postby robleyd » Fri Feb 14, 2020 7:49 am

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
User avatar
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 )

Postby cliffb » Fri Feb 14, 2020 9:45 am

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

Postby Zizi64 » Fri Feb 14, 2020 9:59 am

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

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

Postby robleyd » Fri Feb 14, 2020 10:04 am

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

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

Postby cliffb » Fri Feb 14, 2020 10:18 am

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

Postby robleyd » Fri Feb 14, 2020 10:35 am

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


Return to Calc

Who is online

Users browsing this forum: No registered users and 23 guests