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

 

[Dave Peterson]

How about using a different worksheet with just the formulas in them:

=--('c:\Traffic\[Day1.xls]!A4=0)

And use 30/31 rows and then put =sum(a1:a31) in B1.

Then use =sheet2!b1

in your other worksheet????

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