SUMIFS Exclusions

[Martin Jones]

Hello,
I’m hoping someone can help me with this formula.
=SUMIFS($P$1:$P$99999,$A$1:$A$99999,"<>"&$X$18,$B$1:$B$99999,$U$17,$H$1:$H$99999,$U$1)
This works however it only excludes X18. What I want to do is exclude a range. For instance the exclusion would be &$X$18: $X$26
I have tried this:
=SUMIFS($P$1:$P$99999,$A$1:$A$99999,"<>"&$X$18:$X$26,$B$1:$B$99999,$U$17,$H$1:$H$99999,$U$1)
But I receive a SPILL
Also tried this:
=SUMIFS($P$2:$P$100000,$A$2:$A$100000,"<>"&X17&X18&X19&X20&X21&X22&X23&X24&X25&X26,$B$2:$B$100000,$U$27,$H$2:$H$100000,$U$2)
It will only exclude the first cell i.e., &X17
Thanks in advance
Martin

 

[Martin Jones]

I don't think col X is part of the data, it looks to be a list of the values to exclude.

Correct

 

[Fluff]

Are you sure that there are rows which match all criteria?

Can you post some sample data.
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.

 

[Martin Jones]

So if the values in Column X are normally blank unless you want to exclude them then you could:
=SUMIFS($P$1:$P$99999,$X$1:$X$99999,"<>",$B$1:$B$99999,$U$17,$H$1:$H$99999,$U$1)
This excludes non blanks in column X

Column X will always have values that need to be excluded from Column A. There is no blanks in Column A or X

 

[Martin Jones]

Are you sure that there are rows which match all criteria?

Can you post some sample data.
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.

Positive, they are matching values.
I can't install any add-ins on my corporate system.
However, I can generate a sterilized version of the file and send to my home system. It will take some time to generate.

 

[Fluff]

Ok, that's fine. I'm logging off for the night now, but I'll have a look tomorrow.

 

[Martin Jones]

SEPTEMBER_Ad Hoc Expenditure Report striped.xlsx
	A	B	C	D	E	F	G	H	I	J
1	COST CENTER	OBJECT CLASS	ACTIVITY	TOTAL OBLIGATION AMOUNT		Program	JP102X
2	J00000	000000	JP102X	$0.00			310026	#NAME?	This Calculation
3	J00000	000000	JP102X	$0.00
4	J00000	000000	JP102X	$0.00						CC Exclusion
5	J90702	310026	JP102X	$0.00						J90702
6	J90714	000000	JP102X	$0.00						J90714
7	J90734	000000	JP102X	$0.00						J90734
8	J90761	111001	JP108X	$2,546.04						J90761
9	J90111	111001	JP108X	($2,546.04)						J90111
10	J90102	310026	JP108X	$5,618.88
11	J90102	111001	JP108X	($5,618.88)
12	J90102	111001	JP108X	$8,428.32
13	J90102	111001	JP108X	($8,428.32)
14	J90102	111001	JP108X	$9,389.60
15	J90102	310026	JP108X	($9,389.60)
16	J90702	111001	JP108X	$9,389.60
17	J90714	111001	JP108X	$9,389.60
18	J90734	111001	JP108X	$9,389.60
19	J90761	111001	JP108X	($9,389.60)
20	J90111	111001	JP108X	$9,389.60
aru1633356081350 Report

Cell Formulas
Range	Formula
H2	H2	=SUM(_xlfn._xlws.FILTER($D$1:$D$99998,(ISNA(MATCH($A$1:$A$99998,$J$6:$J$14,0))*($B$1:$B$99998=$G$27)*($C$1:$C$99998=$G$1))))

 

[Fluff]

Thanks for that, however row 5 is the only row that meats the criteria in G1 & G2, so the formula returns 0.
If the criteria range for col A is changed from I6:I14 to I5:I14 I get the #calc error as row 5 is then excluded.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	COST CENTER	OBJECT CLASS	ACTIVITY	TOTAL OBLIGATION AMOUNT		Program	JP102X
2	J00000	0	JP102X	0			310026	0	This Calculation
3	J00000	0	JP102X	0				#CALC!
4	J00000	0	JP102X	0						CC Exclusion
5	J90702	310026	JP102X	0						J90702
6	J90714	0	JP102X	0						J90714
7	J90734	0	JP102X	0						J90734
8	J90761	111001	JP108X	2546.04						J90761
9	J90111	111001	JP108X	-2546.04						J90111
10	J90102	310026	JP108X	5618.88
11	J90102	111001	JP108X	-5618.88
12	J90102	111001	JP108X	8428.32
13	J90102	111001	JP108X	-8428.32
14	J90102	111001	JP108X	9389.6
15	J90102	310026	JP108X	-9389.6
16	J90702	111001	JP108X	9389.6
17	J90714	111001	JP108X	9389.6
18	J90734	111001	JP108X	9389.6
19	J90761	111001	JP108X	-9389.6
20	J90111	111001	JP108X	9389.6
Main

Cell Formulas
Range	Formula
H2	H2	=SUM(FILTER($D$1:$D$99998,(ISNA(MATCH($A$1:$A$99998,$J$6:$J$14,0))*($B$1:$B$99998=$G$2)*($C$1:$C$99998=$G$1))))
H3	H3	=SUM(FILTER($D$1:$D$99998,(ISNA(MATCH($A$1:$A$99998,$J$5:$J$14,0))*($B$1:$B$99998=$G$2)*($C$1:$C$99998=$G$1))))