Greetings all. I am very new to worksheet formulas and I am having trouble
getting sumproduct to work. here is my formula, as I found it in many places
he

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

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 toolsformula auditingevaluate formula to see the steps unfold.

Epinn

"Greg Snidow" wrote in message ...
Greetings all. I am very new to worksheet formulas and I am having trouble
getting sumproduct to work. here is my formula, as I found it in many places
he

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

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" wrote in message ...
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 toolsformula auditingevaluate formula to see the steps unfold.

Epinn

"Greg Snidow" wrote in message ...
Greetings all. I am very new to worksheet formulas and I am having trouble
getting sumproduct to work. here is my formula, as I found it in many places
he

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

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" wrote in message ...
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 toolsformula auditingevaluate formula to see the steps unfold.

Epinn

"Greg Snidow" wrote in message ...
Greetings all. I am very new to worksheet formulas and I am having trouble
getting sumproduct to work. here is my formula, as I found it in many places
he

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

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/micros...4ad71c9e0ba655

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" wrote in message ...
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" wrote in message ...
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 toolsformula auditingevaluate formula to see the steps unfold.

Epinn

"Greg Snidow" wrote in message ...
Greetings all. I am very new to worksheet formulas and I am having trouble
getting sumproduct to work. here is my formula, as I found it in many places
he

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

Hi Greg

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


--
Regards

Roger Govier


"Greg Snidow" wrote in message
...
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" wrote in message
...
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 toolsformula auditingevaluate formula to see the steps
unfold.

Epinn

"Greg Snidow" wrote in message
...
Greetings all. I am very new to worksheet formulas and I am having
trouble
getting sumproduct to work. here is my formula, as I found it in
many places
he

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

Thanks Roger for also giving your input. I see from the other post that it
can get heated. In any event I tried 1*myrange, and I still get a '0'. I
have tried formating both the cell with the formula and the range to text and
then general, but still '0'. I have tried deleting the contents then
formating, then refreshing my data as EPINN suggested, bit still wont work.
Is there maybe something to do with importing from a stored procedure? Since
sumif works, is there a way to use sumif with two conditions in two different
columns? Thank you for all your help.

"Roger Govier" wrote:

Hi Greg

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


--
Regards

Roger Govier


"Greg Snidow" wrote in message
...
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" wrote in message
...
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 toolsformula auditingevaluate formula to see the steps
unfold.

Epinn

"Greg Snidow" wrote in message
...
Greetings all. I am very new to worksheet formulas and I am having
trouble
getting sumproduct to work. here is my formula, as I found it in
many places
he

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

Hello Greg, if you only have the formula in one cell then you won't need the
$ signs. These "fix" the cell references so that if you fill the formula down
or across they don't change.

As written your formula will multiply H33 by N33 by AF33 by AR33 when D31
and E31 meet your criteria and so on down the rows, i.e. you have an "offset"
of two rows. Is that what you require?

If you want to add the values in those columns when the criteria is met for
the same row perhaps

=SUMPRODUCT(--(D31:D10000="Jan"),--(E31:E10000=2006),H31:H10000+N31:N10000+AF31:AF100 00+AR31:AR10000)

"Greg Snidow" wrote:

Greetings all. I am very new to worksheet formulas and I am having trouble
getting sumproduct to work. here is my formula, as I found it in many places
he

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