Sumproduct gives #VALUE

  • Thread starter Thread starter Phil_S
  • Start date Start date
P

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?
 
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
 
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
 
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)
 
Back
Top