SUMIFS with OR and Date range

Annie Dunn
Annie Dunn ✭✭
edited 12/09/19 in Formulas and Functions

I am referencing another sheet for this, and this formula currently works...

=SUMIFS({SALES Range 1}, {SALES Range 5}, "DK", {SALES Range 2}, "Don Joy", {SALES Range 3}, "paid", {SALES Range 4}, >=DATE(2019, 1, 1))

That being said, I need to embed a logical expression as to include multiple criteria for {SALES Range 2}, and to change the >= date into a range with an end date. I try to follow other formulas online but they aren't working for me. Here's what I've tried most recently; I'm VERY new to spreadsheets to this could be really far off:

=SUMIFS({SALES Range 1}, {SALES Range 5}, "DK", {SALES Range 2}, "Don Joy", (OR({SALES Range 3} = "paid", {SALES Range 3} = "billing paperwork submitted - pending payment")) (AND({SALES Range 4}, >=DATE(2019, 1, 1), {SALES Range 4} ,<=DATE(2019,3,31))))

Key:

{SALES Range 1} = Charges

{SALES Range 5} = Rep 

{SALES Range 2} = Billed By

{SALES Range 3} = Status

{SALES Range 4} = Date of Service

 Please help!!!

Thanks 

Comments

  • Mike L.
    Mike L. ✭✭✭

    https://help.smartsheet.com/function/sumifs

    at the bottom of the page: this will help the most 

    =SUMIFS(Cost:Cost, Quantity:Quantity, >15, Warehouse:Warehouse, "A", Item:Item, OR(@cell = "Shirt", @cell = "Socks"))

    I suggest you test each condition by itself before you combined them all into a single formula.  SUMIFS can handle multiple conditions but it still works with a single condition. This way you can nail down the syntax for each condition then combine them.  It's easy to forget a comma so a few spaces here and there make it more readable. 

    =SUMIFS({SALES Range 1}, {SALES Range 5}, "DK")

    then test the next one .....

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You are just putting your OR and AND in the wrong spots is all. Give this a whirl...

     

    =SUMIFS({SALES Range 1}, {SALES Range 5}, "DK", {SALES Range 2}, "Don Joy", {SALES Range 3}, OR(@cell = "paid", @cell = "billing paperwork submitted - pending payment"), {SALES Range 4}, AND(@cell >= DATE(2019, 1, 1), @cell <=DATE(2019,3,31))

     

    To use and AND or OR withing a SUMIFS (or COUNTIFS), you establish your range separately since it is the same range. The OR or AND belongs to the criteria, so it would look something like this...

     

    =SUMIFS({range to be summed}, {1st criteria range}, OR(@cell = {1st criteria for 1st range}, @cell = {2nd criteria for first range}))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • This worked!!! THANK YOU THANK YOU THANK YOU. 

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!