# SUMIFS that can include blank cells

#### Funktion

##### New Member
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Peter_SSs

##### MrExcel MVP, Moderator
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

##### New Member
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

##### New Member
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
43.7 KB · Views: 11
• Dummy Data for Funktion - Charges.png
67.1 KB · Views: 12

#### Fluff

##### MrExcel MVP, Moderator

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

#### Funktion

##### New Member
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.

#### Fluff

##### MrExcel MVP, Moderator

Glad we could help & thanks for the feedback.

#### Funktion

##### New Member
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))

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

#### Attachments

• Dummy Data for Funktion - Summary 2.png
56.2 KB · Views: 4
• Dummy Data for Funktion - Charges 2.png
104.8 KB · Views: 4

#### Fluff

##### MrExcel MVP, Moderator
As this is a totally different question, it needs a new thread. Thanks

#### Funktion

##### New Member
As this is a totally different question, it needs a new thread. Thanks
Roger that.

Replies
3
Views
108
Replies
4
Views
121
Replies
1
Views
122
Replies
8
Views
241
Replies
3
Views
93

1,147,564
Messages
5,741,856
Members
423,691
Latest member

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

### Which adblocker are you using?

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

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