Formula problem

[mjones]

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

 

[T. Valko]

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.

 

[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 -----

 

[Joe User]

Errata....

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

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

 

[Jacob Skaria]

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

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

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.

.

 

[mjones]

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

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:

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

.

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]

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

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

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:

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

.

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

 

[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

Biff would have meant to post the below

If this post helps click Yes

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 -----
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
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 Dto
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

Yep, that did it Biff. I used the last one even though there wasn't
any text. Thanks!

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

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

Biff would have meant to post the below

If this post helps click Yes

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

Yep, that did it Biff. I used the last one even though there wasn't
any text. Thanks!