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")
Answers
-
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?
-
Thank you. I figured it out. After testing each formula independently, I realized my mistake of summing the wrong column. I appreciate your help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!