Hi Greg

Well I think "Jan" was fairly obvious. Highlighting the cell with Jan in
it, showed Jan, whereas if it had been a date (Numeric) formatted as
mmm, in the formula bar I would have seen 01/01/06 or something similar.

2006 and 2007 looked like numeric (they are right justified, not that
that can be taken as definitive - but its a first indication)
Hovering over 2 cells (E10:E11) in the column, the function towards
bottom right of the screen was displaying Sum = 4012 so it was pretty
obviously numeric format. I could have used a blank cell somewhere on
the sheet and typed =E10+1 and had it been text I would have had a
#VALUE result, as it is numeric then I got 2007.

As I said in my post directly back to you, your formula failed on 2
counts
1. Using "2007" as the comparator for a range of data containing numeric
values
2. Even with the correct comparator of 2007, the True values were not
being coerced to 1's as you were not preceding the test with the double
unary minus, nor were you multiplying the tests together with "*" as the
operator rather than ","

As far as differences then occurring with the working formula's result,
and that gained by doing a filter on the respective columns and adding
the respective Subtotal(9,range) results, that was due to the last terms
in the formula being ranges from 12 to 1002 as opposed to correctly
being 10 to 1000.

The underlying format of the cell is not necessarily an indicator of the
cell contents.
With a cell formatted General, entering the word Test or 1234 does not
change its format from the default of General.
However, entering something like 20/11/2006 does change the format to
Date, or entering 17:30 does change the format to Custom hh:mm.

If you then enter 1234 into the cell which has picked up the Date
format, it will return 18/05/1903 or 1234 days after 31/12/1899 (and its
format remains as Date), because it assumes you are entering a serial
number which formatting is supposed to turn into a Date appearance.
Entering the word Test, shows as Test but the format still shows as
Date.
In the cell that has picked up the Date format, entering Test shows Test
and format remains as Custom hh:mm, entering 1234 shows 00:00.
Frustrating isn't it?

So don't look at the cell format to necessarily determine the type of
the content, use a simple test.

If you don't know whether the values are going to be Text or numeric,
then adding the null string "" to each side of the test will cause it to
work always, as you are forcing like for like comparisons of text.

=SUMPRODUCT(--(D10:D1000&""="Jan"),--(E10:E1000&""=2007&""),
G10:G1000+J10:J1000+S10:S1000+Y10:Y1000)
or
=SUMPRODUCT(--(D10:D1000&""="Jan"),--(E10:E1000&""="2007"),
G10:G1000+J10:J1000+S10:S1000+Y10:Y1000)
will work regardless.

--
Regards

Roger Govier


"Greg Snidow" wrote in message
...
Thank you so much Roger. I never would have been able to discern such
subtle
differences. I am just beginning to understand what is going on in
the
background, so I think it will be best if I just repost the old
function
along with the new.

OLD(Does not work)
=SUMPRODUCT(--($D$10:$D$1000 = "Jan"),($E$10:$E$1000 =
"2007"),G10:G1000+J10:J1000+S12:S1002+Y12:Y100 2)

NEW(Works,Thanks Roger)
=SUMPRODUCT(--(D10:D1000="Jan"),--(E10:E1000=2007),G10:G1000+J10:J1000+S10:S1000+Y10 :Y1000)

I can see the differences, my only question now is that if the format
of
both "Jan" and 2007 are general, how did you know they were being
treated
differently?

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