Re: SUMPRODUCT trouble

Tech-Archive recommends: Speed Up your PC by fixing your registry

I thank you for your post which led me to various experiments. As a result, I have discovered a few things about SUMPRODUCT. I am no expert in Excel but I can share with you what I have learned in the past few days about SUMPRODUCT.

SUMPRODUCT is not as "forgiving" as SUMIF or COUNTIF. I totally understand why SUMIF works for you but not SUMPRODUCT. SP is very disciplined about data type. You must compare text to text and numeric values to numeric values. If your data is text then you must test for text in your formula.

The following thread has lots of good info on SUMPRODUCT and data mismatch and how to make the formulae work by coercion etc. Look for Harlan's and Roger's posts. I also posted my experiments there.

http://groups.google.ca/group/microsoft.public.excel.work***.functions/browse_frm/thread/6bf7cf24e6368f2/d14ad71c9e0ba655?tvc=1&hl=en#d14ad71c9e0ba655

or http://tinyurl.com/yf6a9v



This is an excerpt from one of Harlan's post.



"--x is guaranteed to convert text representations of numbers to their numeric values (with possible
floating point rounding/truncation error) while leaving numeric values unchanged, and x&"" is guaranteed to convert numeric values to their text representations while leaving text as-is."



Bottom line is you have to have data type matching otherwise your SP won't work.



I didn't mean that by shrinking your data set, the formula would work. The only reason I suggested you use a sample data set was for you to use the tool "evaluate formula" to debug. It should give you some hint where the problem lies. Have you tried that? I would use about 10 records; 200 may be too much.



If you still can't solve your problem after reading the thread, feel free to post back. Hopefully, an Excel guru can provide you with more specific guidance.



This is a good link to learn about SUMPRODUCT. http://www.xldynamic.com/source/xld.SUMPRODUCT.html



I don't feel comfortable using a function unless I understand it.



Good reading!



Epinn


"Greg Snidow" <GregSnidow@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:BC5F1980-DAD2-45EE-A679-E012EA55D5D9@xxxxxxxxxxxxxxxx
Thank you so much for your time and input. I maybe should have included in
my first post that I used import data to import the data from a stored
procedure on the back end of an .ADP database with SQL2k. The "Jan" is
actually
SELECT...
CASE WHEN DATEPART(MM, ecd) = '1' THEN 'Jan' END. The underlying data is
from a smalldatetime format on the backend. Would this make a difference? I
tested out sumif on the same data and it worked. I am concerned about having
to format the cells more than once, because I need to set the data range
properties to refresh data on file open. I thank you so much for your help.
Oh, also I did try to make it work by restricting the range to only 200 rows,
but it still did not work.

"Epinn" wrote:

Wonder why. <<

I did more testing and I think I am on to something.

If a cell is formatted as TEXT and you key in 2006, it will be treated as TEXT. If you include ="2006" (i.e. with quotes) in your formula, it will find a match. No problem.

But if you take the default format of a cell which is general, key in 2006, then format the cell to TEXT *after* you have entered the value, it will NOT be treated as TEXT. If you use the ISTEXT( ) to determine the format, it will return FALSE. In this case, ="2006" (with quotes) in the formula won't be able to find a match because the value is not text.

If you key in "apple" instead of a number, it will be treated as TEXT regardless.

I am going to start my own thread and hopefully find out why this is happening. Interesting.

Epinn

"Epinn" <someone@xxxxxxxxxxxxxxxxxxx> wrote in message news:er6RavsCHHA.144@xxxxxxxxxxxxxxxxxxxxxxx
I could only get your formula to work by formatting the year column to General and then taking out the " " around 2006 in the formula i.e. =2006 instead of ="2006".

Wonder why.

You can adjust your formula to try out on a smaller data range, say 5 rows and 4 columns. Then click on the cell that holds the formula and click tools>formula auditing>evaluate formula to see the steps unfold.

Epinn

"Greg Snidow" <GregSnidow@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:0952B758-C9BD-4D6F-94DB-F3C0317E4978@xxxxxxxxxxxxxxxx
Greetings all. I am very new to work*** formulas and I am having trouble
getting sumproduct to work. here is my formula, as I found it in many places
here:

=SUMPRODUCT(--($D$31:$D$10000="Jan"),--($E$31:$E$10000="2006"),H33:H10002,N33:N10002,AF33:AF10002,AR33:AR10002)

basically I have a month and year column, both formatted as text, then
several other columns formatted as currency, and some as number. All I get
are zeros. Thank you in advance, I am flumoxed. Also what does the $ do.







.

Quantcast