Desparate..please help

C

Carlee

Hello all

I use the following function on my laptop and it works beautifully:

=SUMPRODUCT(--('Daily Reading Master
Log'!B$3:B$375>=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B$3:B$375<=DATEVALUE("31/01")),'Daily Reading Master Log'!BM$3:BM$375)

When i send the workbook to my the user, all he sees is VALUE! in the boxes
where this function is used.

Why?
How can i fix this?
 
J

JE McGimpsey

His date settings are probably in m/d/y format, so DATEVALUE("31/1")
throws a #VALUE! errror. Try:

=SUMPRODUCT(--('Daily Reading Master
Log'!B$3:B$375>=DATE(YEAR(TODAY()),1,1)), --('Daily Reading Master
Log'!B$3:B$375<=DATE(YEAR(TODAY()),1,31)), 'Daily Reading Master
Log'!BM$3:BM$375)
 
C

Carlee

Hi there,
THanks for the quick response. Using what you have provided, i am now
getting a 'Ref!' error.

Ideas for the desparate one?
 
P

Peo Sjoblom

In your formula that worked, just replace

DATEVALUE("01/01")


with


DATE(YEAR(TODAY()),1,1))

do the same for the last of January as well


you probably get the ref error because you copied John's formula and somehow
you got an extra space or something in the sheet name string. A ref error
basically tells you that the reference is non existent meaning you refer to
a sheet or a cell that doesn't exist


--


Regards,


Peo Sjoblom
 
Top