View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
PAS PAS is offline
external usenet poster
 
Posts: 26
Default Cumulative total with date criteria

Is there a way to simplify the following formula:

=IF(AD2="",NA(),SUMPRODUCT(--(MONTH(Orders!$B$5:$B$65536)=MONTH($AD$2)),--(DAY(Orders!$B$5:$B$65536)=DAY($AD2)),--(YEAR(Orders!$B$5:$B$65536)=YEAR($AD2)),Orders!$J$ 5:$J$65536))

Col "B" = Date in sheet "Orders"
Col "J" = hours for each task in sheet "Orders"
Col "AD" = date where the data is collated

I have one years of data and this formula seems to be slowing the
calculations down alot. Would using define option be the way to go? or am I
stuck with this!!!!