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

[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]

[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 17 times

[robleyd]

Try this:

Code: Select all   Expand viewCollapse view

=SUMIF(A2:A10;">"&TODAY()-30;B2:B10)

[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!

[Zizi64]

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.

[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

[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.

[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.