SUMPRODUCT Query with example book att

**jtwork** · 06-05-2007, 10:15 AM

I cant get the cell in column J to pick up on the data in my sumproduct query.....PLEASE HELP

Book attached

**arthurbr** · 06-05-2007, 10:22 AM

No book is attached to your post!

**jtwork** · 06-05-2007, 10:39 AM

here you go

**NBVC** · 06-05-2007, 10:44 AM

Try changing formula to:

Please Login or Register  to view this content.

**jtwork** · 06-05-2007, 10:46 AM

i tried this method but without the +0 what is the +0 doing and why do i need it?

**NBVC** · 06-05-2007, 10:47 AM

Originally Posted by jtwork

i tried this method but without the +0 what is the +0 doing and why do i need it?

It converts the date which is text (because of the quotes) to a numeric.

**NickiName** · 06-05-2007, 11:24 AM

What does the -- do in the formula?

**NBVC** · 06-05-2007, 11:41 AM

Originally Posted by NickiName

What does the -- do in the formula?

coerces the True/False results in each array to 1's and 0's, respectively, so that the arithmetic can be performed per Sumproduct() functionality.

**NickiName** · 06-05-2007, 01:10 PM

Thanks... Very useful!

**jtwork** · 06-06-2007, 03:34 AM

Is there a way to reffer to a cell for the date ratehr than placing the date in quotes and +0

ive tried to datevalue() the cell and add 0 and have also tried to text(date,"dd/mm/yy")+0

both dont seem to have worked?

**NBVC** · 06-06-2007, 08:04 AM

Originally Posted by jtwork

Is there a way to reffer to a cell for the date ratehr than placing the date in quotes and +0

ive tried to datevalue() the cell and add 0 and have also tried to text(date,"dd/mm/yy")+0

both dont seem to have worked?

You should be able to do that with just a cell reference:

e.g.

Please Login or Register  to view this content.

where X1 houses the cell with a legitimate date in it.

**jtwork** · 06-06-2007, 09:19 AM

i sorted this in the end and could have sorted this out on my had i realised that the data exported by access was actually output as text even though it was numeric.