why does this formula return a #VALUE! error
 

Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values greater than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

why does this formula return a #VALUE! error
 

The two ranges are not the same size
E103:P114 is 12 by 12 while E98 by P98 is 12 by 1
Tell us more about the project's data layout
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dave F" wrote in message
...
Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values greater
than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

why does this formula return a #VALUE! error
 

I can't reproduce the problem.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave F" wrote in message
...
Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values greater
than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

why does this formula return a #VALUE! error
 

Any errors in any of those cells?

And non-numeric data in the first range?

Dave F wrote:

Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values greater than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

--

Dave Peterson

why does this formula return a #VALUE! error
 

I think I figured out the problem here. Some cells in the first column on
the range E103:P114 are blank. Entering 0 in those blank cells eliminated
the error.

Dave
--
Brevity is the soul of wit.


"Bernard Liengme" wrote:

The two ranges are not the same size
E103:P114 is 12 by 12 while E98 by P98 is 12 by 1
Tell us more about the project's data layout
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dave F" wrote in message
...
Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values greater
than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

why does this formula return a #VALUE! error
 

Turns out that the issue is some of the cells in the left-most column of the
range E103:P114 were blank; changing these blank cells to 0 eliminated the
problem.

Dave
--
Brevity is the soul of wit.


"Bob Phillips" wrote:

I can't reproduce the problem.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave F" wrote in message
...
Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values greater
than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

why does this formula return a #VALUE! error
 

Non-numeric, yes. Blanks, to be specific. See my responses to Bob and
Bernard. Thanks for the help.

Dave
--
Brevity is the soul of wit.


"Dave Peterson" wrote:

Any errors in any of those cells?

And non-numeric data in the first range?

Dave F wrote:

Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values greater than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

--

Dave Peterson

why does this formula return a #VALUE! error
 

Not a problem Bernard, but it is one of those cases where -- will not work,
but * does (don't mention this to RagDyer)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bernard Liengme" wrote in message
...
The two ranges are not the same size
E103:P114 is 12 by 12 while E98 by P98 is 12 by 1
Tell us more about the project's data layout
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dave F" wrote in message
...
Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values greater
than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

why does this formula return a #VALUE! error
 

Dave, assuming I have blank and text in the range the sumproduct formula is
pulling from, and that I can't change the ranges, is there anyway to avoid
the associated error by modifying the sumproduct formula? Thanks. Steve C

"Dave Peterson" wrote:

Any errors in any of those cells?

And non-numeric data in the first range?

Dave F wrote:

Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values greater than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

--

Dave Peterson

why does this formula return a #VALUE! error
 

I agree with Bob, I couldn't duplicate your problem.

BUT ... now we can get into XL semantics ... where I have a disagreement
with Harlan.

What do *you* mean by "blank"
That is, as opposed to "empty".

Do you have formulas that equate to zero length strings ( "" or nulls)?
That *to me* is "blank".
OR
Do the cells contain *nothing* ... truly virgin?
That to me is *empty*.

I couldn't duplicate your problem with *EMPTY* cells within E103:P114.
Your original formula worked perfectly.

However, when I entered zero length strings ( "" ), your formula returned
the #VALUE! error.

If you have formulas that equate to zero length strings ( "" or nulls)
within the data range, and you might wish to keep those cells *blank* in
appearance, as opposed to displaying 0's, you might try this formula:

=SUMPRODUCT((E98:P98="Project")*(E103:P114<""),E1 03:P114)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Dave F" wrote in message
...
Turns out that the issue is some of the cells in the left-most column of the
range E103:P114 were blank; changing these blank cells to 0 eliminated the
problem.

Dave
--
Brevity is the soul of wit.


"Bob Phillips" wrote:

I can't reproduce the problem.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave F" wrote in message
...
Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values greater
than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

why does this formula return a #VALUE! error
 

See RagDyer's response. Blank cells do not cause a problem, but spaces or
cells with zero-length strings do. You can circumvent with

=SUMPRODUCT((N(E103:P314))*(E98:P98="Project"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"SteveC" wrote in message
...
Dave, assuming I have blank and text in the range the sumproduct formula
is
pulling from, and that I can't change the ranges, is there anyway to avoid
the associated error by modifying the sumproduct formula? Thanks. Steve
C

"Dave Peterson" wrote:

Any errors in any of those cells?

And non-numeric data in the first range?

Dave F wrote:

Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values greater
than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

--

Dave Peterson

why does this formula return a #VALUE! error
 

Rag--re semantics: as far as I can tell the cells were zero length strings,
which, to me, seems empty. The workbook was inherited from someone else and
perhaps these cells originally contained something.

I have no idea.

But as I say, inserting 0 into these blank/empty/null/whatever cells
eliminated the problem.
--
Brevity is the soul of wit.


"RagDyeR" wrote:

I agree with Bob, I couldn't duplicate your problem.

BUT ... now we can get into XL semantics ... where I have a disagreement
with Harlan.

What do *you* mean by "blank"
That is, as opposed to "empty".

Do you have formulas that equate to zero length strings ( "" or nulls)?
That *to me* is "blank".
OR
Do the cells contain *nothing* ... truly virgin?
That to me is *empty*.

I couldn't duplicate your problem with *EMPTY* cells within E103:P114.
Your original formula worked perfectly.

However, when I entered zero length strings ( "" ), your formula returned
the #VALUE! error.

If you have formulas that equate to zero length strings ( "" or nulls)
within the data range, and you might wish to keep those cells *blank* in
appearance, as opposed to displaying 0's, you might try this formula:

=SUMPRODUCT((E98:P98="Project")*(E103:P114<""),E1 03:P114)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Dave F" wrote in message
...
Turns out that the issue is some of the cells in the left-most column of the
range E103:P114 were blank; changing these blank cells to 0 eliminated the
problem.

Dave
--
Brevity is the soul of wit.


"Bob Phillips" wrote:

I can't reproduce the problem.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave F" wrote in message
...
Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values greater
than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

why does this formula return a #VALUE! error
 

I can't seem to get that to work Bob!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Bob Phillips" wrote in message
...
See RagDyer's response. Blank cells do not cause a problem, but spaces or
cells with zero-length strings do. You can circumvent with

=SUMPRODUCT((N(E103:P314))*(E98:P98="Project"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"SteveC" wrote in message
...
Dave, assuming I have blank and text in the range the sumproduct formula
is
pulling from, and that I can't change the ranges, is there anyway to
avoid
the associated error by modifying the sumproduct formula? Thanks. Steve
C

"Dave Peterson" wrote:

Any errors in any of those cells?

And non-numeric data in the first range?

Dave F wrote:

Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values
greater
than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

--

Dave Peterson

why does this formula return a #VALUE! error
 

Odd, worked for me. I will re-check in the morning, late here now.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RagDyer" wrote in message
...
I can't seem to get that to work Bob!
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Bob Phillips" wrote in message
...
See RagDyer's response. Blank cells do not cause a problem, but spaces
or
cells with zero-length strings do. You can circumvent with

=SUMPRODUCT((N(E103:P314))*(E98:P98="Project"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"SteveC" wrote in message
...
Dave, assuming I have blank and text in the range the sumproduct
formula
is
pulling from, and that I can't change the ranges, is there anyway to
avoid
the associated error by modifying the sumproduct formula? Thanks.
Steve
C

"Dave Peterson" wrote:

Any errors in any of those cells?

And non-numeric data in the first range?

Dave F wrote:

Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values
greater
than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

--

Dave Peterson

why does this formula return a #VALUE! error
 

BTW, I couldn't get the suggestion that you made to work, on the basis that
the numbers returned the #VALUE, so trying to outsort it in a condition made
no difference. Will re-try that also.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RagDyer" wrote in message
...
I can't seem to get that to work Bob!
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Bob Phillips" wrote in message
...
See RagDyer's response. Blank cells do not cause a problem, but spaces
or
cells with zero-length strings do. You can circumvent with

=SUMPRODUCT((N(E103:P314))*(E98:P98="Project"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"SteveC" wrote in message
...
Dave, assuming I have blank and text in the range the sumproduct
formula
is
pulling from, and that I can't change the ranges, is there anyway to
avoid
the associated error by modifying the sumproduct formula? Thanks.
Steve
C

"Dave Peterson" wrote:

Any errors in any of those cells?

And non-numeric data in the first range?

Dave F wrote:

Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values
greater
than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

--

Dave Peterson

why does this formula return a #VALUE! error
 

The above post was 4:08 PM my time.
What was your time of the post?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Bob Phillips" wrote in message
...
BTW, I couldn't get the suggestion that you made to work, on the basis
that
the numbers returned the #VALUE, so trying to outsort it in a condition
made
no difference. Will re-try that also.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RagDyer" wrote in message
...
I can't seem to get that to work Bob!
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Bob Phillips" wrote in message
...
See RagDyer's response. Blank cells do not cause a problem, but spaces
or
cells with zero-length strings do. You can circumvent with

=SUMPRODUCT((N(E103:P314))*(E98:P98="Project"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"SteveC" wrote in message
...
Dave, assuming I have blank and text in the range the sumproduct
formula
is
pulling from, and that I can't change the ranges, is there anyway to
avoid
the associated error by modifying the sumproduct formula? Thanks.
Steve
C

"Dave Peterson" wrote:

Any errors in any of those cells?

And non-numeric data in the first range?

Dave F wrote:

Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values
greater
than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

--

Dave Peterson

why does this formula return a #VALUE! error
 

00:08am. That means that you are on the West Coast?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RagDyer" wrote in message
...
The above post was 4:08 PM my time.
What was your time of the post?
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Bob Phillips" wrote in message
...
BTW, I couldn't get the suggestion that you made to work, on the basis
that
the numbers returned the #VALUE, so trying to outsort it in a condition
made
no difference. Will re-try that also.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RagDyer" wrote in message
...
I can't seem to get that to work Bob!
--
Regards,

RD


-------------------------------------------------------------------------
-
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

-------------------------------------------------------------------------
-
-
"Bob Phillips" wrote in message
...
See RagDyer's response. Blank cells do not cause a problem, but
spaces
or
cells with zero-length strings do. You can circumvent with

=SUMPRODUCT((N(E103:P314))*(E98:P98="Project"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"SteveC" wrote in message
...
Dave, assuming I have blank and text in the range the sumproduct
formula
is
pulling from, and that I can't change the ranges, is there anyway to
avoid
the associated error by modifying the sumproduct formula? Thanks.
Steve
C

"Dave Peterson" wrote:

Any errors in any of those cells?

And non-numeric data in the first range?

Dave F wrote:

Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values
greater
than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

--

Dave Peterson

why does this formula return a #VALUE! error
 

Yes, the "Left" coast, among these "dang" Liberals in the L.A. area.

Heck ... now I've revealed my politics as well as my address!<bg
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Bob Phillips" wrote in message
...
00:08am. That means that you are on the West Coast?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RagDyer" wrote in message
...
The above post was 4:08 PM my time.
What was your time of the post?
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Bob Phillips" wrote in message
...
BTW, I couldn't get the suggestion that you made to work, on the basis
that
the numbers returned the #VALUE, so trying to outsort it in a condition
made
no difference. Will re-try that also.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RagDyer" wrote in message
...
I can't seem to get that to work Bob!
--
Regards,

RD


-------------------------------------------------------------------------
-
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

-------------------------------------------------------------------------
-
-
"Bob Phillips" wrote in message
...
See RagDyer's response. Blank cells do not cause a problem, but
spaces
or
cells with zero-length strings do. You can circumvent with

=SUMPRODUCT((N(E103:P314))*(E98:P98="Project"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"SteveC" wrote in message
...
Dave, assuming I have blank and text in the range the sumproduct
formula
is
pulling from, and that I can't change the ranges, is there anyway to
avoid
the associated error by modifying the sumproduct formula? Thanks.
Steve
C

"Dave Peterson" wrote:

Any errors in any of those cells?

And non-numeric data in the first range?

Dave F wrote:

Formula is: =SUMPRODUCT((E103:P114)*(E98:P98="Project"))

Should return 0. But I also get the error if there are values
greater
than
zero in the range which meet the criteria.

Thanks, Dave
--
Brevity is the soul of wit.

--

Dave Peterson