Code a long array formula

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

 

[Dave Peterson]

If I were using VBA...

Take a look at Dick Kusleika's site:
http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

It's a technique to workaround that .formulaarray length limit.

 

[Per Jessen]

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.