VBA Sum....

[John]

We currently have a formula that Sums a cell (!$A$3) from 15 different excel
files, eventually I would like it to grow to 31 (days of the month)

I would like to make my Formula a little easier to view / manage...
Can I create a function that would do the Sum for me?

Currently my formula looks like this:
=SUM(('c:\Traffic\[Day1.xls]!A4=0)+'c:\Traffic\[Day2.xls]!A4=0)+'c:\Traffic\
[Day3.xls]!A4=0)+'c:\Traffic\[Day4.xls]!A4=0)+'c:\Traffic\[Day5.xls]!A4=0)+'
c:\Traffic\[Day6.xls]!A4=0)+'c:\Traffic\[Day7.xls]!A4=0)+'c:\Traffic\[Day8.x
ls]!A4=0)+'c:\Traffic\[Day9.xls]!A4=0)+'c:\Traffic\[Day10.xls]!A4=0)+'c:\Tra
ffic\[Day11.xls]!A4=0)+'c:\Traffic\[Day12.xls]!A4=0)+'c:\Traffic\[Day13.xls]
!A4=0)+'c:\Traffic\[Day14.xls]!A4=0)+'c:\Traffic\[Day15.xls]!A4=0))

Currently we have staff counting how many cars run certain traffic signs. 0
meaning the cars obeyed the traffic sign.

Thanks,


Ian

 

[Tom Ogilvy]

If you put the sheets all in one workbook (the workbook with the summary
sheet) you could do

=sum(Day1ay31!A4)

--
Regards,
Tom Ogilvy

We currently have a formula that Sums a cell (!$A$3) from 15 different excel
files, eventually I would like it to grow to 31 (days of the month)

I would like to make my Formula a little easier to view / manage...
Can I create a function that would do the Sum for me?

Currently my formula looks like this:
=SUM(('c:\Traffic\[Day1.xls]!A4=0)+'c:\Traffic\[Day2.xls]!A4=0)+'c:\Traffic\[Day3.xls]!A4=0)+'c:\Traffic\[Day4.xls]!A4=0)+'c:\Traffic\[Day5.xls]!A4=0)+'c:\Traffic\[Day6.xls]!A4=0)+'c:\Traffic\[Day7.xls]!A4=0)+'c:\Traffic\[Day8.xls]!A4=0)+'c:\Traffic\[Day9.xls]!A4=0)+'c:\Traffic\[Day10.xls]!A4=0)+'c:\Traffic\[Day11.xls]!A4=0)+'c:\Traffic\[Day12.xls]!A4=0)+'c:\Traffic\[Day13.xls]
!A4=0)+'c:\Traffic\[Day14.xls]!A4=0)+'c:\Traffic\[Day15.xls]!A4=0))

Currently we have staff counting how many cars run certain traffic signs. 0
meaning the cars obeyed the traffic sign.

Thanks,


Ian

 

[John]

Well I attempted to update one of my formulas, and I ran into the issue of
my Formula is too big for the field....

Ian

Well, You COULD write a formula to do what you suggest. It is possible, but
not recommended..

There may be more details that I'm unaware of, but based on what you said in
the post, I would put ALL the months data in one workbook, on one tab. Add a
column called DateStamp.

It is a VERY good practice to keep the data on a tab all by itself. Very
raw. No formatiing and very accurate.

Use other worksheet tabs to make fancy reports..

From there you can do all kinds of alalytics with built in excel
functionality.

IF sums are positional (ie have to add $a3 on each sheet.) I'd at least
keep all data in one workbook and have a different tab for each day...
Adding those will be easier too (*** there is a LOT of background overhead
with each formula that links to another file. I imagine your workbook is
noticably slow, and will get slower if you add 16 more files it needs to
open) The complexity of the function you suggest is very easy and if
implemented the right way, shouldn't even take a second to recalc
everythign...

HTH..

PS once you get to proper table design in excel, think about moving data
storage to a database..

We currently have a formula that Sums a cell (!$A$3) from 15 different excel
files, eventually I would like it to grow to 31 (days of the month)

I would like to make my Formula a little easier to view / manage...
Can I create a function that would do the Sum for me?

Currently my formula looks like this:
=SUM(('c:\Traffic\[Day1.xls]!A4=0)+'c:\Traffic\[Day2.xls]!A4=0)+'c:\Traffic\
[Day3.xls]!A4=0)+'c:\Traffic\[Day4.xls]!A4=0)+'c:\Traffic\[Day5.xls]!A4=0)+'
c:\Traffic\[Day6.xls]!A4=0)+'c:\Traffic\[Day7.xls]!A4=0)+'c:\Traffic\[Day8.x
ls]!A4=0)+'c:\Traffic\[Day9.xls]!A4=0)+'c:\Traffic\[Day10.xls]!A4=0)+'c:\Tra
ffic\[Day11.xls]!A4=0)+'c:\Traffic\[Day12.xls]!A4=0)+'c:\Traffic\[Day13.xls]
!A4=0)+'c:\Traffic\[Day14.xls]!A4=0)+'c:\Traffic\[Day15.xls]!A4=0))

Currently we have staff counting how many cars run certain traffic signs. 0
meaning the cars obeyed the traffic sign.

Thanks,


Ian

 

[JMB]

Also, I believe the SUM function only accepts 30 arguments.

 

[Tom Ogilvy]

formulas are restricted to 1024 characters, measured when the formula is in
R1C1 format.

--
Regards,
Tom Ogilvy

Well I attempted to update one of my formulas, and I ran into the issue of
my Formula is too big for the field....

Ian

Well, You COULD write a formula to do what you suggest. It is possible, but
not recommended..

There may be more details that I'm unaware of, but based on what you

said

in
the post, I would put ALL the months data in one workbook, on one tab. Add a
column called DateStamp.

It is a VERY good practice to keep the data on a tab all by itself. Very
raw. No formatiing and very accurate.

Use other worksheet tabs to make fancy reports..

From there you can do all kinds of alalytics with built in excel
functionality.

IF sums are positional (ie have to add $a3 on each sheet.) I'd at least
keep all data in one workbook and have a different tab for each day...
Adding those will be easier too (*** there is a LOT of background overhead
with each formula that links to another file. I imagine your workbook is
noticably slow, and will get slower if you add 16 more files it needs to
open) The complexity of the function you suggest is very easy and if
implemented the right way, shouldn't even take a second to recalc
everythign...

HTH..

PS once you get to proper table design in excel, think about moving data
storage to a database..

=SUM(('c:\Traffic\[Day1.xls]!A4=0)+'c:\Traffic\[Day2.xls]!A4=0)+'c:\Traffic\[Day3.xls]!A4=0)+'c:\Traffic\[Day4.xls]!A4=0)+'c:\Traffic\[Day5.xls]!A4=0)+'c:\Traffic\[Day6.xls]!A4=0)+'c:\Traffic\[Day7.xls]!A4=0)+'c:\Traffic\[Day8.xls]!A4=0)+'c:\Traffic\[Day9.xls]!A4=0)+'c:\Traffic\[Day10.xls]!A4=0)+'c:\Traffic\[Day11.xls]!A4=0)+'c:\Traffic\[Day12.xls]!A4=0)+'c:\Traffic\[Day13.xls]

!A4=0)+'c:\Traffic\[Day14.xls]!A4=0)+'c:\Traffic\[Day15.xls]!A4=0))

Currently we have staff counting how many cars run certain traffic signs. 0
meaning the cars obeyed the traffic sign.

Thanks,


Ian