Can SUMPRODUCT be used when only two ranges are specified?

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127
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...
 

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127

ADVERTISEMENT

=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
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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.
 

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127
Got it working now. Redefined the arrays so the sizes are equal, and corrected the match formula. Very nice.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,323
Messages
5,600,954
Members
414,417
Latest member
Nobu

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top