Cumulative total with date criteria

[Pas]

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!!!!

 

[Mike H]

Hi,

The formula could be simplified to this

=IF(AD2<>"",SUMPRODUCT((Orders!$B$5:$B$65536=AD2)*(Orders!$J$5:$J$65536)),NA())

But I would make 2 points

1. Don't wrap in it an if formula just to return NA use this and it will
return zero of AD2 is empty
=SUMPRODUCT((Orders!$B$5:$B$65536=AD2)*(Orders!$J$5:$J$65536))

2. Shorten the range, do you really need ~65 thousand rows

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Pas]

Thank you Mike,
That seems to work well (your second formula). It has speed thing up.