# Can SUMPRODUCT be used when only two ranges are specified?

#### psulion01

##### Board Regular
I can't get the following formula to work... is it possible?

SUMPRODUCT((Range1<>Range2)*(DollarValue))

Basically I have a master sheet of data:

Range1 DollarValue
AAA 20
BBB 35
CCC 5
DDD 10

and a separate sheet with items i want excluded from the calc:

Range2
BBB
DDD

I want the SUMPRODUCT to give me a value of 25 (AAA + CCC, excluding BBB and DDD from the calc). However, I'm getting #N/A in my formula cell. Is there an easy way to do this other than specifying each value to exclude, e.g. SUMPRODUCT((Range1<>"BBB")*(Range1<>"DDD")*(DollarValue))

I have a lot of values to exclude, so this could get very hard to manage...

thanks,
Mike

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi Mike

Excel Workbook
ABCDE
1AAA20FFF108
2BBB35DDD
3CCC5GGG
4DDD10
5EEE43
6FFF100
7GGG60
8HHH5
Sheet2

Still getting #N/A. When I manually filter the master data i can get a sum of what I want, so I don't think I have a corrupt or invalid cell in the range. Any thoughts on why i may be getting the #N/A? The formula makes sense to be, but Excel doesn't seem to like it...

What exact formula are you using? The formula did work in my Excel.

=SUMPRODUCT(NOT(ISNUMBER(MATCH(ExclRegions,data2007_Region,0)))*data2007_MarketValue)

where:

ExclRegions = range of regions i want excluded
data2007_Region = range of all regions in data set
data2007_MarketValue = range of \$ value i want added for all regions not appearing in the ExclRegions range

It works for me as well.

I find the best way to troubleshoot issues like this is to test my formula with the ACTUAL range references, instead of named ranges.

The likely cause of the issue is that the named ranges
ExclRegions and data2007_MarketValue are NOT of equal size.

Ranges in SUMPRODUCT formulas must be of equal size.

Mike

You've got that round the wrong way - should be:

Code:
``=SUMPRODUCT(NOT(ISNUMBER(MATCH(data2007_Region,ExclRegions,0)))*data2007_MarketValue)``

Which was causing the problem highlighted by Jonmo1 - that of mismatched range sizes.

Got it working now. Redefined the arrays so the sizes are equal, and corrected the match formula. Very nice.

Re: Can SUMPRODUCT be used when only two ranges are specifie

I can't get the following formula to work... is it possible?

SUMPRODUCT((Range1<>Range2)*(DollarValue))

Basically I have a master sheet of data:

Range1 DollarValue
AAA 20
BBB 35
CCC 5
DDD 10

and a separate sheet with items i want excluded from the calc:

Range2
BBB
DDD

I want the SUMPRODUCT to give me a value of 25 (AAA + CCC, excluding BBB and DDD from the calc). However, I'm getting #N/A in my formula cell. Is there an easy way to do this other than specifying each value to exclude, e.g. SUMPRODUCT((Range1<>"BBB")*(Range1<>"DDD")*(DollarValue))

I have a lot of values to exclude, so this could get very hard to manage...

thanks,
Mike

One of:

=SUM(DollarValue)-SUMPRODUCT(SUMIF(Range1,Range2,DollarValue))

=SUMPRODUCT(1-ISNUMBER(MATCH(Range1,Range2,0)),DollarValue)

Re: Can SUMPRODUCT be used when only two ranges are specifie

=SUMPRODUCT(1-ISNUMBER(MATCH(Range1,Range2,0)),DollarValue)

Replies
7
Views
292
Replies
5
Views
441
Replies
5
Views
265
Replies
3
Views
118
Replies
3
Views
296

1,219,770
Messages
6,150,167
Members
450,937
Latest member
kattyg261

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