Code a long array formula

  • Thread starter Thread starter xp
  • Start date Start date
X

xp

Hi,

I'm trying to code a program to write a formula array into a spreadsheet; to
get the right calculation, I need the sum of the values in column "B" that
fall on each date in a monthly period and between two times on each day. The
following formula works fine when manually typed, but is beyond the XL limit
for coding the entry of array formulas:

=SUM(IF(LEFT('UMS-IT'!$L$2:$L$71805,11)="AMSCOT
ARLD",IF(TEXT('UMS-IT'!$M$2:$M$71805,"MM-DD-YYYY HH:MM:SS")>"04-12-2009
17:00:00",IF(TEXT('UMS-IT'!$M$2:$M$71805,"MM-DD-YYYY HH:MM:SS")<"04-12-2009
24:00:01",'UMS-IT'!$B$2:$B$71805))))

Anyone have any ideas as to how I can code this? Or is it possible to
internally calculate and just write the result (if so how?)?

Thanks much for your assistance!
 
Hi

Try this Formula, where B2 and B3 on formula sheet have the start / end date
and time:

=SUMPRODUCT((--LEFT('UMS-IT'!$L$2:$L$100000,11)="AMSCOTARLD"),--(TEXT('UMS-IT'!$M$2:$M$100000,"MM-DD-YYYY
HH:MM:SS">B2)),--(TEXT('UMS-IT'!$M$2:$M$100000;"MM-DD-YYYY
HH:MM:SS"<B3)),'UMS-IT'!$B$2:$B$100000)

Hopes this helps.
 
Back
Top