Unfortunately this does not seem to work. I have succesfully used this
technique a number of times with different sized arrays (but smaller
formulas) so long as they refer to different worksheets. However, I have not
been able to predict if they will work or not; sometimes I merely have to
adjust the size of the array, sometimes I have to change the orientation of
the array from vertical to horizontal. By fiddling with the parameters I can
produce results, but not by any logical path that I can see. Is there a rule
within SUMPRODUCT that seeks certain array sizes or shapes? If so, is it
affecting the aforementioned formula?

Thanks.
"Peo Sjoblom" wrote:

Dave,

you can use transpose as follows

=SUMPRODUCT(--(A1:A10=TRANSPOSE(D1:D3)))

since transpose is involved it needs to be array entered and also note
that transpose can only take a range D1:D3 as opposed to D1,D2,D3

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Dave R." wrote in message
...
Hi - I had nothing to do with creating that page. But I agree it is nice.

I just tried, and Excel doesn't seem to accept cell references in place of
numbers. Maybe someone can chime in with a way to pull off the (working)
equivilant of:

=SUMPRODUCT(--(A1:A10={d1,d2,d3})) (which does not work) to count values
in
A1:A10 that match the content of either D1, D2 or D3.






"Cornelius" wrote in message
...

Thanks Dave. I read your page on xlDynamic, very nice. Do I understand
that if I input the conditional cells in the {x,y,z} format rather than
(x:z)
this will work?

"Dave R." wrote:

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.