SUMIFS that can include blank cells

Funktion

New Member
Joined
Mar 25, 2016
Messages
31
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I'm trying various SUMIF and SUMIFS (and even SUMPRODUCT) formulas to get total charges on specific account numbers.

I have a worksheet with two tabs; one is a Summary of charges per account, and the other contains all of the Charges on all of the accounts.

The account numbers are comprised of up to five separate segments. Those segments are in columns A, B, C, D, and E on both tabs.

There will always be something in column A.
There will always be something in column B.
Columns C, D and E may or may not contain numbers. There could be one, two or all three of them populated on various accounts.

The problem is that any formula I try doesn't like blank cells and returns $0.00 if any of the cells in columns C, D and E in the account number are blank.

How can I build a formula that will either ignore the blanks, or match them up between tabs and provide a sum of all relevant charges, even if not all five of the fields contain numbers/data?

A sample of one formula I've tried in the summary tab is (the sum range is in column F):
=SUMIFS(Charges!$F$2:$F$1000,Charges!$A$2:$A$1000,A2,Charges!$B$2:$B$1000,B2,Charges!$C$2:$C$1000,C2,Charges!$D$2:$D$1000,D2,Charges!$E$2:$E$1000,E2)

If any of the cells C2, D2, or E2 on either tab are blank, the formula returns $0.00, even when there are dollar amounts in several cells that match the currently populated A and B segments (or A, B, and C. Or A, B, D, and E...).
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

If you use XL2BB to post a small set of dummy data and the expected results then we won't have to guess what the data is really like or have to type it out manually to test formulas. :)
 
Upvote 0
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

If you use XL2BB to post a small set of dummy data and the expected results then we won't have to guess what the data is really like or have to type it out manually to test formulas. :)
Thank you for the suggestions, Peter. I forgot those would be useful details. :-) I have updated my account details and will put together a small set of dummy data and upload that today. Stay tuned...
 
Upvote 0
Thank you for the suggestions, Peter. I forgot those would be useful details. :) I have updated my account details and will put together a small set of dummy data and upload that today. Stay tuned...
I have created a set of dummy data but it looks like I am unable to install the XL2BB add-in as this is a work computer that is administered by my employer (I have little control over what's on it). I'll have to do the upload later tonight unless there's another way to upload.
I will upload a couple of images now that may help. I have color coded the different accounts to more easily group them visually for this example. The only account that returns the correct result is the one where ALL FIVE segments are populated with numbers. The formula that is showing in the formula bar on the Summary tab is in cell F3.
 

Attachments

  • Dummy Data for Funktion - Summary.png
    Dummy Data for Funktion - Summary.png
    43.7 KB · Views: 116
  • Dummy Data for Funktion - Charges.png
    Dummy Data for Funktion - Charges.png
    67.1 KB · Views: 119
Upvote 0
How about
Excel Formula:
=SUMIFS(Charges!$F$2:$F$1000,Charges!$A$2:$A$1000,A3,Charges!$B$2:$B$1000,B3,Charges!$C$2:$C$1000,C3&"",Charges!$D$2:$D$1000,D3&"",Charges!$E$2:$E$1000,E3&"")
 
Upvote 1
Solution
How about
Excel Formula:
=SUMIFS(Charges!$F$2:$F$1000,Charges!$A$2:$A$1000,A3,Charges!$B$2:$B$1000,B3,Charges!$C$2:$C$1000,C3&"",Charges!$D$2:$D$1000,D3&"",Charges!$E$2:$E$1000,E3&"")
Fluff!! You are awesome! That works beautifully! Thank you so much. Your work here is done. :)
 
Upvote 1
Glad we could help & thanks for the feedback.
 
Upvote 0
Hi Gang, Not sure if you'll see this tacked onto to this thread or if I should start a new one, but I'll start here.

Same workbook as yesterday, but I expanded the dummy data a bit. Each account has an Account Holder Name associated with it which resides on the Summary sheet. I want to have Excel find the appropriate account holders name and bring it into column H on the Charges tab for each charge there. I tried an Index and Match (which is not my comfort zone) and it wants to spill and doesn't work... Here's the formula I tried
=INDEX(Summary!G3:G10,MATCH(Summary!A3:A10&Summary!B3:B10&Summary!C3:C10&Summary!D3:D10&Summary!E3:E10,0))

I'm uploading images of the two tabs (can't download XL2BB on this computer).

Not sure what approach to take on this one. Any advice would be greatly appreciated.
 

Attachments

  • Dummy Data for Funktion - Summary 2.png
    Dummy Data for Funktion - Summary 2.png
    56.2 KB · Views: 24
  • Dummy Data for Funktion - Charges 2.png
    Dummy Data for Funktion - Charges 2.png
    104.8 KB · Views: 23
Upvote 0
As this is a totally different question, it needs a new thread. Thanks
 
Upvote 0

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top