Cumulative total with date criteria

  • Thread starter Thread starter Pas
  • Start date Start date
P

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!!!!
 
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.
 
Back
Top