Code a long array formula
 

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!

Code a long array formula
 

If I were using VBA...

Take a look at Dick Kusleika's site:
http://www.dailydoseofexcel.com/arch...rmulas-in-vba/

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

xp wrote:

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

Code a long array formula
 

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.

---
Per

"xp" skrev i meddelelsen
...
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!