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

Dave French

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")

Carson Penticuff

Edited out my previous comment. After rereading you post a few times, your formula seems to be correct. Have you tried running each of the three individual SUMIFS independently for troubleshooting purposes?

Dave French

Thank you. I figured it out. After testing each formula independently, I realized my mistake of summing the wrong column. I appreciate your help.