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]
[Solved] Help me fix "=+SUMIF(... ;" >TODAY()-30" ;...)"
[Solved] Help me fix "=+SUMIF(... ;" >TODAY()-30" ;...)"
Last edited by cliffb on Fri Feb 14, 2020 9:47 am, edited 1 time in total.
OpenOffice 4.1.7 on Windows 7
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:
Here is my sample file:
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.
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.
Re: Help me fix "=+SUMIF(A8:A50 ;" >TODAY()-30" ; B8:B50 )
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
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
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!
Thanks again for the help!
OpenOffice 4.1.7 on Windows 7
Re: [SOLVED]Help me fix "=+SUMIF(... ;" >TODAY()-30" ;...)"
The
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.
Code: Select all
" >TODAY()-30"
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.
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.
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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
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.
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
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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers