Formula problem
mjones
Here's another I hope you can help with.
Here's my formula that works fine:
{=SUMPRODUCT(--($B$6:$B$417<DATE(2009,10,1)),--($Q$6:$Q$417=B463),$D
$6:$D$417)}
Now I want to change the end to ,$D$6:$O$417)}, but changing the D to
an O gives the old #VALUE!.
I don't suspect the cells in the formula because I'm using them with
other similar formulas.
Any help would be appreciated.
Thanks,
Michele
T. Valko
Just a normal ENTER will do. No need to array enter.
=SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q$417=B463)*$D$6:$O$417)
Note that if there's any TEXT in the range $D$6:$O$417 you'll get an error.
--
Biff
Microsoft Excel MVP
"mjones" <mic...@quality-computing.com> wrote in message
news:7f0aa57f-8011-4d5b...@j19g2000yqk.googlegroups.com...
Joe User
> Try it like this...
> [....]
> =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q$417=B463)*$D$6:$O$417)
> Note that if there's any TEXT in the range $D$6:$O$417 you'll get an
> error.
.... Which we can avoid by using something like the original form, namely:
=SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q$417=B463),$D$6:$O$417)
Since the OP was complaining of #VALUE errors, I don't see why you would
steer him in a direction that might exacerbate the problem.
----- original message -----
"T. Valko" <biffi...@comcast.net> wrote in message
news:eOMJFc2...@TK2MSFTNGP04.phx.gbl...
Joe User
I wrote:
> "T. Valko" <biffi...@comcast.net> wrote:
>> Try it like this...
>> [....]
>> =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q$417=B463)*$D$6:$O$417)
>> Note that if there's any TEXT in the range $D$6:$O$417 you'll get an
>> error.
> [....]
> Since the OP was complaining of #VALUE errors, I don't see why you would
> steer him in a direction that might exacerbate the problem.
My mistake: I see now that your formulation might solve the OP's problem.
At least it does address the original reason for the #VALUE error. Mea
culpa!
I would have explained that the original #VALUE error resulted from the fact
that the dimensions of the arrays were incompatible. As the SUMPRODUCT Help
page explains: "The array arguments must have the same dimensions. If they
do not, SUMPRODUCT returns the #VALUE! error value."
It might be helpful to Michele. It certainly would have avoided my mistake.
----- original message -----
"Joe User" <joeu2004> wrote in message
news:eCgMt02Y...@TK2MSFTNGP06.phx.gbl...
Jacob Skaria
=SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q$417=B463)
*ISNUMBER($D$6:$O$417),$D$6:$O$417)
If this post helps click Yes
---------------
Jacob Skaria
"Joe User" wrote:
> "T. Valko" <biffi...@comcast.net> wrote:
> > Try it like this...
> > [....]
> > =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q$417=B463)*$D$6:$O$417)
> > Note that if there's any TEXT in the range $D$6:$O$417 you'll get an
> > error.
>
> ..... Which we can avoid by using something like the original form, namely:
> .
>
mjones
<JacobSka...@discussions.microsoft.com> wrote:
> Biff would have meant to post the below
>
> =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q$417=B463)
> *ISNUMBER($D$6:$O$417),$D$6:$O$417)
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
> "Joe User" wrote:
> > > Try it like this...
> > > [....]
> > > =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q$417=B463)*$D$6:$O$417)
> > > Note that if there's any TEXT in the range $D$6:$O$417 you'll get an
> > > error.
>
> > ..... Which we can avoid by using something like the original form, namely:
>
> > =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q$417=B463),$D$6:$O$417)
>
> > Since the OP was complaining of #VALUE errors, I don't see why you would
> > steer him in a direction that might exacerbate the problem.
>
> > ----- original message -----
>
> >news:eOMJFc2...@TK2MSFTNGP04.phx.gbl...
> > > Try it like this...
>
> > > Just a normal ENTER will do. No need to array enter.
>
> > > =SUMPRODUCT(($B$6:$B$417<DATE(2009,10,1))*($Q$6:$Q$417=B463)*$D$6:$O$417)
>
> > > Note that if there's any TEXT in the range $D$6:$O$417 you'll get an
> > > error.
>
> > > --
> > > Biff
> > > Microsoft Excel MVP
>
> > >news:7f0aa57f-8011-4d5b...@j19g2000yqk.googlegroups.com...
> > >> Hi All,
>
> > >> Here's another I hope you can help with.
>
> > >> Here's my formula that works fine:
>
> > >> {=SUMPRODUCT(--($B$6:$B$417<DATE(2009,10,1)),--($Q$6:$Q$417=B463),$D
> > >> $6:$D$417)}
>
> > >> Now I want to change the end to ,$D$6:$O$417)}, but changing the D to
> > >> an O gives the old #VALUE!.
>
> > >> I don't suspect the cells in the formula because I'm using them with
> > >> other similar formulas.
>
> > >> Any help would be appreciated.
>
> > >> Thanks,
>
> > >> Michele
>
> > .
Oh my. I'm scaring myself here. The formula works!
I'm revamping a crazy spreadsheet to manage my companies finances
because the old one just wasn't cutting it. I've added some columns
and rows so their different from the last formula. I'm calculating
values by quarters so the last formula above was for the first quarter
(before October 1st). Now I realize I need to do it again, but
between two dates.
Here is the old formula:
=SUMPRODUCT(--($B$6:$B$484>DATE(2009,9,30)),--($B$6:$B$484<DATE
(2010,1,1)),--($V$6:$V$484=$B510),$H$6:$H$484)
I need to extend the end $H$6:$H$484 to be $H$6:$S$484. Please help
because this is Greek to me. Plus I've been up half the night three
nights in a row trying to do this and my brain is fried. Although
someone explained the double dash thing before, I don't have the
background to understand the explanation.
Many thanks! I teach project management and always tell my students
how great this board is and how you've saved my skin repeatedly!
Michele
T. Valko
If there is no TEXT in the range $H$6:$S$484
=SUMPRODUCT(($B$6:$B$484>DATE(2009,9,30))*($B$6:$B$484<DATE
(2010,1,1))*($V$6:$V$484=$B510)*$H$6:$S$484)
If there might be TEXT in the range $H$6:$S$484
=SUMPRODUCT(($B$6:$B$484>DATE(2009,9,30))*($B$6:$B$484<DATE
(2010,1,1))*($V$6:$V$484=$B510)*(ISNUMBER($H$6:$S$484)),$H$6:$S$484)
--
Biff
Microsoft Excel MVP
"mjones" <mic...@quality-computing.com> wrote in message
news:6b71a37c-c384-48e8...@r31g2000vbi.googlegroups.com...
mjones
> Try one of these...
>
> If there is no TEXT in the range $H$6:$S$484
>
> =SUMPRODUCT(($B$6:$B$484>DATE(2009,9,30))*($B$6:$B$484<DATE
> (2010,1,1))*($V$6:$V$484=$B510)*$H$6:$S$484)
>
> If there might be TEXT in the range $H$6:$S$484
>
> =SUMPRODUCT(($B$6:$B$484>DATE(2009,9,30))*($B$6:$B$484<DATE
> (2010,1,1))*($V$6:$V$484=$B510)*(ISNUMBER($H$6:$S$484)),$H$6:$S$484)
>
> --
> Biff
> Microsoft Excel MVP
>
Yep, that did it Biff. I used the last one even though there wasn't
any text. Thanks!
T. Valko
--
Biff
Microsoft Excel MVP
"mjones" <mic...@quality-computing.com> wrote in message
news:4e8218dc-6c31-4b4a...@g23g2000vbr.googlegroups.com...