SUMPRODUCT & Dynamic Range

  • Thread starter Thread starter elumbukoodu
  • Start date Start date
E

elumbukoodu

okay i'm having problems with this sumproduct formula

=SUMPRODUCT((MONTH="Jan")*(SALES<1000))

here MONTH and SALES are my dynamic ranges.

and i'm getting this #REF! error

i just want to count the number of times the sales were less than 1000
in the SALES field when the MONTH field is January

the 'MONTH' part worked fine this other formula

=SUMPRODUCT((MONTH="Jan")*(ASSOSIATE="Jane"))

so i guess the problem is with the "numerical dynamic range" SALES!!!

the formulae i have used for naming MONTH and SALES are

=OFFSET($W$1,0,0,MATCH("*",$W:$W,-1))

=OFFSET($M$1,0,0,MATCH(1E+306,$M:$M))

respectively!!!

both of which i think works fine coz the i see the selection show the
right range.

Somebody Help Me Please !!!
 
Thanks for the reply Peo!!!

Well if u're meaning the length of the range (number of entries/rows to
which they extend to, in both the ranges) . . . .YES!! they are same!
.. .and there are no exceptional items like text or blank cells.

i tried all possible permutations using the " in the formula.

any insight anybody ???
 
Redefine your dynamic ranges...

Define first BigNum as referring to:

9.99999999999999E+307

Define LastRow as referring to:

=MATCH(BigNum,x!$M:$M)

where you need to replace x with the actual sheet name.

Define SALES as referring to:

=x!$M$1:INDEX(x!$M:$M,LastRow)

Define MONTH as referring to:

=x!$W$1:INDEX(x!$W:$W,LastRow)

Now...

=SUMPRODUCT((MONTH="Jan")*(SALES<1000))

should work as expected, that is, deliver a multiconditional count.

The above scheme assumes the SALES range to house as many entries as the
MONTH range. If that's not the case, post back with info about the most
complete range (column) among the ranges of interest along with the kind of
data this range houses.
 
Just as a further check, you might want to go from dynamic range names to
fixed range names. (I would do this on a duplicate or spare copy.) Does
the formula work? If not, then investigate as to why not. If it does, the
reintroduce "Month" and check. No--find out why and fix. Yes, reintroduce
Sales.

All I am suggesting is to try to narrow down your error source. Your
formula as written appears okay. So something in either one of the two
ranges must not be correct.

Hope that leads to some discovery.

Regards,
Kevin
 
Try Aladin's solution first, but another way of being sure the ranges are the
same size, is to use pretty much the same formula to determine the height, but
specify a different starting cell, eg using your formulas:-

=OFFSET($W$1,0,0,MATCH("*",$W:$W,-1))

=OFFSET($M$1,0,0,MATCH("*",$W:$W,-1))

or

=OFFSET($M$1,0,0,MATCH(1E+306,$M:$M))

=OFFSET($W$1,0,0,MATCH(1E+306,$M:$M))

Haven't tested either, but I think the logic should hold good.
 
Ken,

That MATCH formula would give an erroneous result if there is a formula
blank (that is, "") early in the range of interest.

Aladin

Ken Wright said:
Try Aladin's solution first, but another way of being sure the ranges are the
same size, is to use pretty much the same formula to determine the height, but
specify a different starting cell, eg using your formulas:-

=OFFSET($W$1,0,0,MATCH("*",$W:$W,-1))

=OFFSET($M$1,0,0,MATCH("*",$W:$W,-1))

or

=OFFSET($M$1,0,0,MATCH(1E+306,$M:$M))

=OFFSET($W$1,0,0,MATCH(1E+306,$M:$M))

Haven't tested either, but I think the logic should hold good.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

-------------------------------------------------------------------------- --
Newsgroups - Where you really can get a free lunch!!
-------------------------------------------------------------------------- --
Click to expand...
 
Cheers Aladin - I knew I'd regret not testing :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Newsgroups - Where you really can get a free lunch!!
----------------------------------------------------------------------------



Aladin Akyurek said:
Ken,

That MATCH formula would give an erroneous result if there is a formula
blank (that is, "") early in the range of interest.

Aladin
Click to expand...
 
Back
Top