# Sumif Not in Range

#### Siyanna

##### Well-known Member
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

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
For example, ...?

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

Last edited:

Excel 2010
ABCDEF
1DateCountryInfo BAmount400Condition_List
217-Feb-19Brazil1001500Europe
417-Feb-19China300USA
520-Feb-19Europe400
617-Feb-19Russia500Date
720-Feb-19USA60017-Feb-19
8
9
10E1Date matches and Countries not on Condition_List.
11E2Amount - Countries on Condition_List.
12E3Amount - Countries not on Condition_List.
13
5a
Cell Formulas
RangeFormula
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
NameRefers ToCells
Condition_List='5a'!\$F\$2:\$F\$4

Last edited:
Brill Thank you

Replies
3
Views
221
Replies
2
Views
419
Replies
5
Views
241
Replies
17
Views
329
Replies
5
Views
91

1,207,111
Messages
6,076,614
Members
446,216
Latest member
BEEALTAIR

### 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.

### Which adblocker are you using?

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

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