SUMPRODUCT does not equal Criteria Range

oakwood_001

Board Regular
Joined
Apr 4, 2008
Messages
108
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.
 

Some videos you may like

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
Joined
Aug 18, 2011
Messages
6,458
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 4, 2008
Messages
108
Brilliant - works well - thank you :)

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

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,458
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 4, 2008
Messages
108

ADVERTISEMENT

Thanks FormR! Very appreciated
 

oakwood_001

Board Regular
Joined
Apr 4, 2008
Messages
108
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
Joined
Aug 18, 2011
Messages
6,458
Office Version
  1. 365
Platform
  1. Windows
Hi, try changing "ISERROR" to "ISNUMBER".
 

Watch MrExcel Video

Forum statistics

Threads
1,118,525
Messages
5,572,641
Members
412,478
Latest member
MakeItWorkVBA
Top