why does this formula return a #VALUE! error

[Guest]

Formula is: =SUMPRODUCT((E103114)*(E9898="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

 

[Bernard Liengme]

The two ranges are not the same size
E103114 is 12 by 12 while E98 by P98 is 12 by 1
Tell us more about the project's data layout
best wishes

 

[Bob Phillips]

I can't reproduce the problem.

--
HTH

Bob Phillips

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

 

[Dave Peterson]

Any errors in any of those cells?

And non-numeric data in the first range?

 

[Guest]

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

Dave

 

[Guest]

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

Dave

 

[Guest]

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

Dave

 

[Bob Phillips]

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)

 

[Guest]

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

 

[RagDyeR]

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 E103114.
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((E9898="Project")*(E103114<>""),E103114)

--

HTH,

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

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

Dave

 

[Bob Phillips]

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(E103314))*(E9898="Project"))

--
HTH

Bob Phillips

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

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

 

[Guest]

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.

 

[RagDyer]

I can't seem to get that to work Bob!

 

[Bob Phillips]

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)

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

RD

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

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(E103314))*(E9898="Project"))

--
HTH

Bob Phillips

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

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

 

[Bob Phillips]

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)

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

RD

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

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(E103314))*(E9898="Project"))

--
HTH

Bob Phillips

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

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

 

[RagDyer]

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

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)

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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

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

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]

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

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

--
HTH

Bob Phillips

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

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

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)

------------------------------------------------------------------------- -
-

!