Adding multiple SUMIFS and return is $0 if one cell is blank

I'm trying to add multiple sumifs formulas together in one formula. However, not all of the sumifs will have an input. Example: Expense tracking, most of the time there will be only one charge code to utilize. There are times when multiple charge codes can be utilized and I'm only trying to extract the amount for a specific charge code that may be input in one of three areas (Charge Code (Gas Tax Road Maint), Charge Code (Gas Tax Road Maint) 2, Charge Code (Gas Tax Road Maint) 3). Therefore, I'm summing the total amount from three areas within the sheet that meets the criteria. Below is the formula I'm using, however, if the charge code is not blank in the other reference cells then it returns $0. Any help will be greatly appreciated.

=SUMIFS([Amount 2]:[Amount 2], [Charge Code (Gas Tax Road Maint)]:[Charge Code (Gas Tax Road Maint)], "200.2000.54144 Contract Street Maintenance") + SUMIFS(Amount:Amount, [Charge Code (Gas Tax Road Maint) 2]:[Charge Code (Gas Tax Road Maint) 2], "200.2000.54144 Contract Street Maintenance") + SUMIFS([Amount 3]:[Amount 3], [Charge Code (Gas Tax Road Maint) 3]:[Charge Code (Gas Tax Road Maint) 3], "200.2000.54144 Contract Street Maintenance")

Tags:

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!