Excluding values from a SUM IF formula w/ defined names

[DPO]

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!

 

[Expiry]

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]

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!

=SUM(BPTest4500Cl1GLAccountBalance)-SUMPRODUCT(SUMIF(BPTestGLAccountNumber,ExcludedGLAccounts,BPTest4500Cl1GLAccountBalance))

 

[DPO]

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]

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
	A	B	C	D	E	F	G
1	Accounts		Balance		Total		Exclude
2	5400-1		10				5400-1
3	5410-1		10		10		5500-1
4	5420-1		10		10		6000-1
5	5430-1		10		10		6010-1
6	5440-1		10		10		6050-1
7	5450-1		10		10		6090-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]

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
	A	B	C	D	E	F	G
1	Accounts		Balance		Total		Exclude
2	5400-1		10				5400-1
3	5410-1		10		20		5500-1
4	5410-1		10		20		6000-1
5	5430-1		20		20		6010-1
6	5440-1		10		10		6050-1
7	5450-1		10		10		6090-1
Sheet1