Help with a SUMIFS formula

Beth Fantozzi 1

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

Kelly Moore

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

Beth Fantozzi 1

@Kelly Moore Yes, if the Client Invoice # is missing, the Date of Client Invoice is also missing. Thank you!

Kelly Moore

@Beth Fantozzi 1

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