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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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...
 
Upvote 0
=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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Got it working now. Redefined the arrays so the sizes are equal, and corrected the match formula. Very nice.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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
Back
Top