# SUMPRODUCT does not equal Criteria Range

#### oakwood_001

##### Board Regular
Hi All,

I'm trying to exclude several criterion from a list in a SUMPRODUCT formula with little success.

I've tried doing it using a range in the example shown below:

=SUMPRODUCT(--(Data!\$A\$18:\$A\$130="Ford"),--(Data!\$B\$18:\$B\$130<>S14:S18),--(Data!\$C\$18:\$C\$130=B13),--(Data!D\$18:D\$130))

Where in this example
A:A is a list of car makes
B:B is the model of the car where I want to exclude certain list of values which is in the range S14:S18
C:C is the year of manufacture
D:D is a number range that I want to return

 Car Make Model Year Volume Ford Focus 2009 4 Hyundai i30 2010 7 Mazda MX5 2011 8 Ford Fiesta 2009 10 Ford Mondeo 2008 3

<tbody>
</tbody>

How would I also type this formula by using a manual list of exclusions? e.g. "Focus", "Fiesta", "Mondeo"

Help greatly appreciated.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### FormR

##### MrExcel MVP
Hi, here is one possible option:

Code:
``=SUMPRODUCT(--(Data!\$A\$18:\$A\$130="Ford"),--(ISERROR(MATCH(Data!\$B\$18:\$B\$130,S14:S18,0))),--(Data!\$C\$18:\$C\$130=B13),Data!D\$18:D\$130)``

#### oakwood_001

##### Board Regular
Brilliant - works well - thank you

Could I use the same formula with a 'manual' list?

#### FormR

##### MrExcel MVP
Could I use the same formula with a 'manual' list?

Do you mean like this:

Rich (BB code):
``=SUMPRODUCT(--(Data!\$A\$18:\$A\$130="Ford"),--(ISERROR(MATCH(Data!\$B\$18:\$B\$130,{"Focus","Fiesta","Mondeo"},0))),--(Data!\$C\$18:\$C\$130=B13),Data!D\$18:D\$130)``

#### oakwood_001

##### Board Regular

Thanks FormR! Very appreciated

#### oakwood_001

##### Board Regular
Further to this (FormR or anyone else reading), how would I do a SUMPRODUCT so that a range DOES equal a Criteria Range?

#### FormR

##### MrExcel MVP
Hi, try changing "ISERROR" to "ISNUMBER".

#### oakwood_001

##### Board Regular
Of course! Brilliant - thanks once again FormR!

Replies
0
Views
273
Replies
1
Views
222
Legacy 396030
L
Replies
4
Views
775
Replies
2
Views
346
Replies
2
Views
549