Array Function Question

[Guest]

I have little knowledge of Array Function. I'm updating a spreadsheet
developed by someone else. One array formula trying to lookup data in another
spreadsheet looks like below:

{=SUM(IF([Data]Data!$A$3:$A$70>=DATEVALUE("01/01/2006"),IF(Data]Data!$A$3:$A$70<=DATEVALUE("03/01/2006"),IF(Data]Data!B$1:$AR$1=A5,Data]Data!$B$3:$AR$38))))}

I was told to press Ctrl/Shift/Enter to refrsh the data and it worked last
time(3 months ago). But this time the cell won't refresh but shows the
formula text.

The formula should be fine. Did I miss anything to refresh it?

 

[Peo Sjoblom]

Check the format of the cell with the formula, make sure it isn't text

Also no need to have the date range going to A70 when the data range only
goes to AR38 (test what happens if you put a date the fulfills the condition
in A40)

=SUM(IF($A$3:$A$38>=DATEVALUE("01/01/2006"),IF($A$3:$A$38<=DATEVALUE("03/01/2006"),IF(B$1:$AR$1=A5,$B$3:$AR$38))))

can be shortened a bit to

=SUM(IF($A$3:$A$38>=--"01/01/2006",IF($A$3:$A$38<=--"03/01/2006",IF(B$1:$AR$1=A5,$B$3:$AR$38))))


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com