Sumif Not in Range

[Siyanna]

Hi All

Im trying to do a sum but on if the values are not in my condition list

so sum the total range, if the date column matches the date and the exception is not not in the condition list

whats the best way to do this

Thank you

 

[shg]

For example, ...?

 

[Siyanna]

Sorry

what i mean is that i have a range that i have named conditions

what i want to do is sum the totals column (Col D) if the value in column B copied down is not in the Condition named range

Im trying to avoid multiple Or conditions

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1	Date	Country	Info B	Amount	400	Condition_List
2	17-Feb-19	Brazil		100	1500	Europe
3	20-Feb-19	Canada		200	600	Russia
4	17-Feb-19	China		300		USA
5	20-Feb-19	Europe		400
6	17-Feb-19	Russia		500		Date
7	20-Feb-19	USA		600		17-Feb-19
8
9
10	E1	Date matches and Countries not on Condition_List.
11	E2	Amount - Countries on Condition_List.
12	E3	Amount - Countries not on Condition_List.
13
5a

Cell Formulas
Range	Formula
E1	=SUMPRODUCT(--(A2:A7=F7),--(1-ISNUMBER(MATCH(B2:B7,Condition_List,0))),(D2:D7))
E2	=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B7,Condition_List,0))),(D2:D7))
E3	=SUMPRODUCT(--(1-ISNUMBER(MATCH(B2:B7,Condition_List,0))),(D2:D7))
F7	=A2

Named Ranges
Name	Refers To	Cells
Condition_List	='5a'!$F$2:$F$4

 

[Siyanna]

Brill Thank you