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:
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Expiry

Well-known Member
Joined
Jun 20, 2007
Messages
865
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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))
 

DPO

Board Regular
Joined
Feb 20, 2007
Messages
131
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. ?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,796
Office Version
  1. 2019
Platform
  1. Windows
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.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,796
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,329
Messages
5,600,996
Members
414,419
Latest member
JRDunya

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
Top