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
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!