=SUMPRODUCT Problems with Date

[cjeasom]

Hi All,

Brand new to this, but require some help. Im using the =SUMPRODUCT function.

I have used the =SUMPRODUCT successfully on the same sheet using the following;

=SUMPRODUCT(--(E4:E303="Materials"),(F4:F303))

- This produces the correct results

However when I try the following;

=SUMPRODUCT(--(B4:B303="01/05/2012"),(F4:F303))

- I get no results. The format for the date is 01/05/2012 in cells B4:B303. Now if I change the string above to 01,05,2012 and then cell to 01,05,2012 it will work. However I cannot use the Filters correctly then. Am I missing something glaringly obvious or is it just that the Date with / will not function in this manner.

Any help would be greatly appreciated.

Regards
Chris

[Pepe Le Mokko]

Perhaps =SUMPRODUCT(--(B4:B303=DATEVALUE("01/05/2012"))*(F4:F303))

[cjeasom]

Unfortunately that doesn't work, does anyone know of any other variations?

It would be greatly appreciated

[Fotis1991]

Hi and welcome to the forum.

For me, best way is to use a cell reference for your date.

Type in E1 your date and then use.

=SUMPRODUCT(--(B4:B303=E1),(F4:F303))

[cjeasom]

I think you have misunderstood the above, sorry

[Fotis1991]

..If you think so...

[cjeasom]

The file is on the following link;

http://www.maxsurl.com/link.php?ref=M1PRWTHSAB

Can someone take a look for me?

[NBVC]

Select column B and go to Data|Text to Columns... skip to 3rd window.. and select Date from column data format section, then select appropriate format from drop down beside the Date selection..

click Ok.

Does that fix it?

[cjeasom]

No unfortunately not ;(

[NBVC]

Can you attach the workbook here? use the paperclip icon in the Go Advanced reply window.

[cjeasom]

Attached... Many thanks

[NBVC]

I tried what Fotis1991 suggested, using $ to freeze you ranges, so you can copy down.

=SUMPRODUCT(--($B$4:$B$303=K4),($F$4:$F$303))

copied down.

some are 0 since you don't have corresponding dates... but some have values.

Btw, you have different date formats in column B and column K

[Pepe Le Mokko]

Unfortunately that doesn't work, does anyone know of any other variations?

It would be greatly appreciated

Thank you for kindly indicating where it went wrong

[cjeasom]

Legends! Cheers for this chaps...