Re: SUMPRODUCT trouble

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








.