• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nesting Today() in Sumifs formula to compare Previous Yr

harrismc87

New Member
I am trying to compare YTD sales for specific customers using a sumifs formula without having to update the actual date every time. I am trying to use a today()-365 to return the previous year's date.


=sumifs(Table8[Invoice Amount],Table8[Invoice Date],">=1/1/2010",Table8[Invoice Date],"<=today()-365",Table8[Customer Group],"Customer A")


I am getting a result of $0.00 instead of the amount for which I am searching. When I simply replace the today()-365 with the date I am looking to return, the number shows up.


Can someone help me with this? I am wasting a ton of time fooling with this and I know someone out there has a simple solution.

Thanks
 
Everything inside the quotation marks is being treated as a string/word, and XL is evaluating the text as 0. To join cell reference or math operations with a criteria, concatenate like so:


=sumifs(Table8[Invoice Amount],Table8[Invoice Date],">=1/1/2010",Table8[Invoice Date],"<="&TODAY()-365,Table8[Customer Group],"Customer A")
 
Back
Top