SUMPRODUCT does not equal Criteria Range

[oakwood_001]

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.

 

[FormR]

Hi, here is one possible option:

=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]

Brilliant - works well - thank you

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

 

[FormR]

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

Do you mean like this:

=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]

Thanks FormR! Very appreciated

 

[oakwood_001]

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

 

[FormR]

Hi, try changing "ISERROR" to "ISNUMBER".

 

[oakwood_001]

Of course! Brilliant - thanks once again FormR!