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