Roger. When I tested the formula for only "Jan" it returned the correct
value. When I tested it for only "2007" it returned the correct value after
I removed the "" from 2007. But when I put them both together they do not
work, so I am thinking I have a syntax error. When I did this test I made a
new workbook and did not touch the format of anything. It seems all are
general. This is very perplexing to me, so I thank all of you people very
much who take the time to help foundlings like me.

"Roger Govier" wrote:

Hi Greg

Although you are saying that the column D contains "Jan", is it text
"Jan" or could it be 01/01/2006 formatted as mmm?
What are the lengths returned by the cells in column D, are they all 3 ?
=LEN(D2)

Try breaking the formula down into pieces, and see if any parts return
the correct values e.g.
=SUMPRODUCT(--($D$31:$D$10000&""="Jan"))
=SUMPRODUCT(--($E$31:$E$10000&""="2006"))

Your comparative ranges are not the same as the data ranges to be
summed. They are of equal dimension, so Sumproduct will not object, but
is that what you intended?

If you want to mail me a copy of the workbook, I will take a look. The
previous problem was operator error.
To mail direct, remove NOSPAM from my address.

--
Regards

Roger Govier


"Greg Snidow" wrote in message
...
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.