Sumproduct gives #VALUE

[Phil_S]

I have a spreadsheet where I want to sum the costs based upon a code that
they posses and the date in which the costs are processed.

My spreadsheet looks like this:-

B C D E
F
CODE DATE DESCRIPTION ORDER NO. COST
CS01 14-Aug-08
£10,000
CS02 14-Aug-08
£500
CS01 14-Aug-08
£10,000

I have the formula:

=SUMPRODUCT(--(B2:B4="CS01"),--(C2:C4=DATE(2008,8,14)),(F2:F4)

When I input this formula I recieve a #VALUE? Is there anything that I am
doing wrong?

 

[Max]

Your formula seems ok, except for the extraneous "(" next to F2. I suspect
that your actual data might contain an error: #value! somewhere. Clean it up,
and it'll work ok.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik

 

[Pete_UK]

Perhaps your cost values are text rather than numbers (did you type
the £ sign, rather than use Format | Cell | Currency?). It might also
be that your dates are not really dates but text values that look like
dates.

Hope this helps.

Pete

 

[Don Guillett]

In addition to the other comments, try trimming and using a cell with the
date and delete the (
=SUMPRODUCT(--(trim(B2:B4)="CS01"),--(C2:C4=D1),F2:F4)