You don't have to tell excel what not to calculate except that you wouldn't
include it in what you do want to calculate.

Also, sumproduct only works on arrays of the same size. E78:Q78 contains
many fewer values than K7:K865 does. This seems to be where your NA message
is coming from. If you want to do some sumproduct calculating on a single
range but "looking for" multiple values, try something like

=SUMPRODUCT(--(A1:A10={1,2,3}))
which will count cells in A1:A10 that are equal to either 1, 2, or 3.

Beyond that, not clear what you're doing. Interesting tidbits about sales to
New York and Chicago though.. sounds like business is good.




"Cornelius" wrote in message
...

Hi all. I've been developing a sheet that measures numerous aspects of my
business and allows any set of parameters to be displayed at a single
time.
For instance, one could look at sales to New York on a weekly basis in
2004
through a particular channel or customer, then switch these parameters
using
validated cells to Chicago quarterly 2004 all business.

I've created all the sets of vlookups for a SUMPRODUCT to look at, each
corresponding to a large data grid that has the appropriate categories
(dollars or pounds, weeks, years, customer category, region, etc.). Here
is
the formula:


=SUMPRODUCT(($C22=Data!$M$5:$FN$5)*($O$82=Data!$J$ 7:$J$865)*($A22=Data!$E$7:
$E$865)*($B22=Data!$F$7:$F$865)*($E$78:$Q$78=Data! $K$7:$K$865)*($N$116:$Z$11
6=Data!$M$4:$FN$4)*(NOT($D$132:$AD$132=Data!$B$7:$ B$865))*(NOT($F$52:$H$52=D
ata!$D$7:$D$865))*(NOT($C$88:$H$88=Data!$C$7:$C$86 5))*Data!$M$7:$FN$865)

where Data!M7:FN865 is the data grid. The NOT functions eliminate certain
areas that shouldn't be calculated (i.e. looking at New York you ignore
Chicago, LA and Miami, but looking at all business you ignore nothing).

This formula returns N/A. If I reduce the ranges to be used or ignored
(make $C$88:$H$88 just $C$88) I get a value. How do I overcome this? I
have
used ranges like this in the past succesfully, but never so many.