Help with a SUMIFS formula
Kind Smartsheet Community - I need a hand with a formula. I'm trying to add an invoice amount in another sheet if the project name matches, if the approval column is "Approved" and if the Client Invoice # is blank or if the Date of Client invoice is in the past 5 days and I'm getting an error. I'm not sure if I can put an OR statement in a SUMIFS function so I'm attempting to break them out and combine the 2 SUMIFS into the same cell.
This is the one that is working:
Can someone please help me with the formula that pulls for if the Date of Client invoice was in the past 5 days?
Thank you!!
Answers
-
Hey @Beth Fantozzi 1
A clarification question - if an entry is missing the Client Invoice #, would it also be missing the Date of Client Invoice? If yes, then it would make your SUMIFS more straight-forward.
Kelly
-
@Kelly Moore Yes, if the Client Invoice # is missing, the Date of Client Invoice is also missing. Thank you!
-
Then try this:
You will need to create a new cross sheet reference for your Date of Client Invoice column. Below is the criteria that goes with it. You will add the new reference with it's criteria to the inside of your existing SUMIFS. You do not need an additional SUMIFS.
=SUMIFS(all of your current SUMIFs criteria, {whatever you named your new Date cross reference range}, OR(@cell="", AND(@cell >= TODAY(-5), @cell <= TODAY())))
This OR works because the OR criteria refer to the same range - which is why I asked if the date field would be blank. It would have been less straight forward if the OR referred to a totally different column.
Does this work for you?
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!