Sumproduct Question

[JP]

=SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P252000))

This above formula achieves the desired result. However, when I add
a fourth column (below) I get a Value error.

I think I'm close...any ideas?

=SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P252000)*($M$25:$M$2000))

 

[Dave Peterson]

First, I'd watch those addresses. I bet you want to use $P$25:$P$2000, too.

Second, do you have any text values in M25:M2000--or any errors in that range?

If you have some text, you can use a different formula to ignore text:

=SUMPRODUCT(--($E$25:$E$2000=4),
--($F$25:$F$2000=6.5),
$P$25:$P$2000,
$M$25:$M$2000)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

===
ps. When you're looking for text or errors, remember to look in hidden rows,
too.

 

[JP]

First, I'd watch those addresses. I bet you want to use $P$25:$P$2000, too.

No. It was correct.

Second, do you have any text values in M25:M2000--or any errors in that range?
None.

If you have some text, you can use a different formula to ignore text:

=SUMPRODUCT(--($E$25:$E$2000=4),
--($F$25:$F$2000=6.5),
$P$25:$P$2000,
$M$25:$M$2000)

Doing it this way doesn't return the value error, but it gives
different answers. I tallied the results by hand and for some reason
they are off.

 

[Bob Phillips]

Are you sure that you don't have a #VALUE in one of the cells?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JP]

Yes I'm quite sure. I checked manually, and also many other
computations are used with those columns of data and they return
proper answers.

 

[Dave Peterson]

If you put:
=counta(m25:m2000)
in an empty cell
and
=count(m25:m2000)
in another

What do these formulas return?

 

[Dave Peterson]

Select that range
Edit|goto|special
Check constants and errors (and uncheck everything else)

Then try the same thing with formulas and errors.

If you have the data filtered, make sure you show all the rows.

 

[JP]

I did both and got an answer of "no cells were found."

 

[JP]

221, and 118 respectively.

 

[Dave Peterson]

That means that there are 3 values that are not numbers.

=counta() returns the number of cells with something in them.
=count() returns the number of cells with numbers in them.

Select the range
edit|goto|special
and look for numbers or text or blanks

Look in values or formulas or both.

 

[JP]

OK, checked all of that out. This is the formula I have in the fist
cell.

=IF(I26="","",I26-H26)

I drag it down the column. For every new day that data is entered I
skip a space. I couldn't find the three cells.

 

[Dave Peterson]

Do you have the worksheet filtered?

Maybe your error or text value is in one of the hidden rows???

 

[RagDyer]

You *should not* use the asterisk form of Sumproduct() when you have your
calculating column(s) populated by formulas that may return zero length
strings (nulls - "" ), as you *do* have!
These returns are considered TEXT, and will generate the #Value! error when
Column I is empty, causing a null ( "" ) return.in Column M.

The asterisk form will calculate text numbers, as long as they look like
numbers, but will error out on all other text values that are non-numeric.
The unary form (suggested by Dave), will by-pass text *AND* numeric text,
and just calculate "true" numbers.

So, if your calculations are different using Dave's suggestion, I would
guess that you have *both* numeric and text numbers present in your
datalist.

If you would/could revise your formula to:

=IF(I26="",0,I26-H26)

then you could continue using the asterisk form, since zero would replace
the text null ( "" ).

You might try it and post back with results.

 

[JP]

Not sure I understand all of it, but it worked. CountA and Count both
return 289, so I guess that did the trick. The only problem now is
that $0.00 shows up in the skipped rows, so I guess I'll have to use
condiditional formatting to get rid of them.

Appreciate the advice. Thanks a lot.

 

[JP]

Found a gliltch. Now this formula in a column two columns over no
longer works.

=IF(M28="","",IF(M29="",SUM(M$26:M28)-SUM(O$26:O27),""))

 

[RagDyer]

Look, if data is to be keyed in, or imported in, the asterisk form of
Sumproduct is the way to go ... warns you of mistakes ... BUT ... if you're
populating your calculating columns with formulas returning nulls, you
*should* be using the unary form, as Dave suggested.

Now that you've revised your formula to return zeroes, compare both forms of
the function and see if they both return the same result.

If they don't, that means that you have true numbers, and text that looks
like numbers, in your datalist.
That's not a good thing to have happening, at any time!

You can use a 'helper" column, and reference it to any questionable columns,
to see if all your numbers are numeric numbers.

Simply use this in any vacant column:

=Isnumber(M26)
and drag it down to see if every row returns a TRUE.

If you see a FALSE, that's the row(s) to fix!

On the other hand, if you insist on using the asterisk form, and you're
going to use CF to fix the display, then you'll have to revise *all* your
other formulas to match the revised value of your calculating columns.

FROM:
=IF(M28="","",IF(M29="",SUM(M$26:M28)-SUM(O$26:O27),""))

TO:
=IF(M28=0,0,IF(M29=0,SUM(M$26:M28)-SUM(O$26:O27),0))

It *may* become a nightmare trying to change everything around.

Your choice!

 

[JP]

I had to change the formula in three columns as you suggested to make
everything work. Thanks for your help.

 

[RagDyer]

You're very welcome, and thank you for the feed-back.