Excluding values from a SUM IF formula w/ defined names

DPO

Board Regular
Joined
Feb 20, 2007
Messages
131
Hello. I think this one will be a lay-up for a lot of you, but I just can't seem to get it to work. I am trying to do a SUM IF formula where the formula searches a defined range and sums the values from another range as long as values from a 3rd list are not found in the searched range. The formula I am trying to get to work is:

{=SUM(IF(BPTestGLAccountNumber<>ExcludedGLAccounts,BPTestCl1GLAccountBalance,""))}

Where:
BPTestGLAccountNumber is the range of cells to search on the main tab (A15:A1000 on sheet 1)

ExcludedGLAccounts is a range/list of values to be excluded from the search of BPTest4500GLAccountNumber (D15:D1000 on sheet 2)

BPTestCl1GLAccountBalance is the list of values to be summed (F15:F1000 on sheet 1)

Upon searching other posts, I have also tried to apply the following, which did not work:
=IF(COUNTIF(ExcludedGLAccounts,BPTest4500GLAccountNumber),"",SUM(BPTest4500Cl1GLAccountBalance))

Thanks! :confused:
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Using SUM and IF together is different to using SUMIF.

The SUMIF formula can be used to search on a range (eg. sumif(RANGEA,A1,RANGEB) will sum every amount id RANGEB when the value in A1 occurs in RANGEA on the same row), but using the SUM and IF together in the way you have is basically checking whether the whole of one range is the same as the whole of another range.

I'm not entirely sure what it is you're wanting to do, but if you can give more detail, I'll try and work it out for you.
 
Upvote 0
Hello. I think this one will be a lay-up for a lot of you, but I just can't seem to get it to work. I am trying to do a SUM IF formula where the formula searches a defined range and sums the values from another range as long as values from a 3rd list are not found in the searched range. The formula I am trying to get to work is:

{=SUM(IF(BPTestGLAccountNumber<>ExcludedGLAccounts,BPTestCl1GLAccountBalance,""))}

Where:
BPTestGLAccountNumber is the range of cells to search on the main tab (A15:A1000 on sheet 1)

ExcludedGLAccounts is a range/list of values to be excluded from the search of BPTest4500GLAccountNumber (D15:D1000 on sheet 2)

BPTestCl1GLAccountBalance is the list of values to be summed (F15:F1000 on sheet 1)

Upon searching other posts, I have also tried to apply the following, which did not work:
=IF(COUNTIF(ExcludedGLAccounts,BPTest4500GLAccountNumber),"",SUM(BPTest4500Cl1GLAccountBalance))

Thanks! :confused:

=SUM(BPTest4500Cl1GLAccountBalance)-SUMPRODUCT(SUMIF(BPTestGLAccountNumber,ExcludedGLAccounts,BPTest4500Cl1GLAccountBalance))
 
Upvote 0
Hi. Thanks for the reply. Not sure how to better explain this, but I'll try. The range that is being searched is a series of account #s (column A), and I want to sum the corresponding account balances in column F. I have a list of account numbers (column D on another tab) which I want the formula to exclude these account #s and their respective balances if found w/in the range. I could easily nest the actual account #s to be excluded w/in the formula, however, this list will most likely grow over time and I would like a more dynamic way of managing these exclusions.

I hope that makes it more clear. ?
 
Upvote 0
Heres a try for you. I am using a search() function to tell me if there is not a match - the function returns an error if not found, so the error tells me "good, no match, total up". It's kind of backwards maybe, but in this case an error is good and if search is an error than that means we have a TRUE for summing up: ISERROR(SEARCH()) will return TRUE. Then I use the SUMIF to do the summing. On the other hand, if the search() does not error out, then a match was found on the excluded list...so no sum.
Book1
ABCDEFG
1AccountsBalanceTotalExclude
25400-110 5400-1
35410-110105500-1
45420-110106000-1
55430-110106010-1
65440-110106050-1
75450-110106090-1
Sheet1


Formula in Cell E2 is =IF(ISERROR(SEARCH(A2,$G$2:$G$65536)),SUMIF($A$2:$A$65536,A2,$C$2:$C$65536),"")

I do not have my excluded account numbers on another sheet in this case. You will see that my ranges extend from row 2 to the bottom of the spreadsheet so lots of room to grow.
 
Upvote 0
DPO:
I don't like this, though. See how our totals are "duplicated" - we have an account in the list, and it properly sums the total, but does it for each occasion. Have I misread the structure of your worksheet or is this what you need?

Hope that this helps, or your reply from Jonmo is better than mine.

Regards.
excludedAccounts.xls
ABCDEFG
1AccountsBalanceTotalExclude
25400-110 5400-1
35410-110205500-1
45410-110206000-1
55430-120206010-1
65440-110106050-1
75450-110106090-1
Sheet1
 
Upvote 0

Forum statistics

Threads
1,211,737
Messages
6,103,626
Members
447,873
Latest member
dolphin_tlp

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