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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

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,210
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,386
Messages
5,836,960
Members
430,464
Latest member
nickburrett

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