SUMIFS that can include blank cells

[Funktion]

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

 

[Peter_SSs]

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.

 

[Funktion]

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...

 

[Funktion]

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.

 

[Fluff]

How about

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

 

[Funktion]

How about

=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.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Funktion]

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.

 

[Fluff]

As this is a totally different question, it needs a new thread. Thanks

 

[Funktion]

As this is a totally different question, it needs a new thread. Thanks

Roger that.