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.