Firstly, throw away the quote marks, unless the 4000 and 1 are text strings.
If they are numbers, you don't want quote marks. If your data is pulled in
from another system and contains text strings rather than numbers, you'll
need to be careful and may need to convert the data. If in doubt use
=ISNUMBER(cellref) and =ISTEXT(cellref) to check what type of data you've
got.
Secondly it is probable easier to use =-SUMPRODUCT(...) instead of
=SUMPRODUCT(...)*-1
Thirdly, where you say "... b1 and b2 hold the upper and lower numerical
values for the date range", I hope that you mean "... lower and upper ... "
respectively.
Fourthly, are you confused between columns C and E?
Fifthly it sounds as if you may have been confused when you said "... have
overcome the date issue by creating a seperate spreadsheet with the months
converted to a general format as the sumproduct didnt seem to like the
column being set with a mmyy format."
The format in which you *display* a number doesn't affect the calculation.
What matters is the underlying content of the cell. A date is the same date
whether it is formatted as mmyy or ddd dd mm yyyy or even as General. And
remember that a date formatted as mmyy to show 1208 is *not* the same as a
simple number 1208.
--
David Biddulph

"Confused of Chingford!"
wrote in message ...
Hi Lars
The formula returns an incorrect value, if i filter the original
spreadsheet
and total the value of the columns meeting the criteria i get a different
value to the value returned by my sumproduct statement! I have used "4000"
and "1" but it made no differnce to the result.

Phil

"Lars-Åke Aspelin" wrote:

On Tue, 30 Dec 2008 07:01:01 -0800, Confused of Chingford! <Confused
of wrote:

Hi

I have been attempting to resolve this issue for a while, having looked
both
here and at MediaKent.co.uk
My issue is that i have a worksheet containing financial data in the
following format
Column A = Nominal ledger Code (format nnnn)
Column B = Department Code (format n)
Column C = Transaction Date (format nnnnn)
Column D = Transaction Value (format NNNNN.NN)

I have tried several SumProduct statements, and have overcome the date
issue
by creating a seperate spreadsheet with the months converted to a
general
format as the sumproduct didnt seem to like the column being set with a
mmyy
format.

I need to pull values for particular nominal ledger codes, department
numbers, falling within a date range into a seperate sheet.

I am using the following formula
=SUMPRODUCT((Sheet1!D1:D16201)*(Sheet1!A1:A16201= "4000")*(Sheet1!B1:B16201=1)*(Sheet1!E1:E16201=Sh eet2!B1)*(Sheet1!E1:E16201<=Sheet2!B2))*-1

Where 4000 = nominal code, 1 = department number, and sheet 2 b1 and b2
hold
the upper and lower numerical values for the date range.

Once i have fathomed this bit out i will need to add additional criteria
as
some of the cells will hold the values of multiple nominal codes, and to
ensure that future data that is pulled via ODBC from a sage accounting
system
i will need to include currently empty cells in the calculations.

Any help greatly appreciated and will also prevent me from going
premeturely
bald!!!

You forgot to mention what the problem is with the formula you use.
Does it return the wrong value or does it return an error?
What value do you expect an what do you get.
Why do you use " with the Nominal ledger code ("4000") but not with
the Department code (1)?

Lars-Åke