You need an offset to account for the first non-data columns:

If your first number to use is in Column ???, use
=SUMPRODUCT(--(MOD(COLUMN(5:5)-COLUMN($???$1),12)=0),5:5)

Replace the ??? with the column letter, like this for column B:
=SUMPRODUCT(--(MOD(COLUMN(5:5)-COLUMN($B$1),12)=0),5:5)

So, to get the average, you need to count the filled in cells, so use

=SUMPRODUCT((MOD(COLUMN(5:5)-COLUMN($B$1),12)=0)*5:5)/SUMPRODUCT((MOD(COLUMN(5:5)-COLUMN($B$1),12)=0)*(5:5<"")*1)

HTH,
Bernie
MS Excel MVP


wrote in message
...
Hello,

I am trying to come up with a formula that will help in calculating
averages in my visitor tracking spreadsheet. My spreadsheet has a lot
of visitor demographics and head count data entered by day. Across the
top, I have dates as column labels. Down the side, I have different
visitor demographic age ranges as row labels. On the lowest row, I
have a total of all the visitors in any given day.

I've already figured out the formula to count the different age groups
and genders. Now what I need is a formula to count the total visitors
per day, but end up with totals for all Mondays, Tuesdays,
Wednesdays,etc.......

Other info: We are only open Monday - Saturday, so I only have those
dates across the top.

Here is a screenshot of the spreadsheet:
http://www.flickr.com/photos/jenkob/2628527135/


My ultimate end goal is to be able to get an average number of
visitors for each day of the week. (Example: Average Saturday
visitation- 12 people)

I have tried to modify this formula which I've used to count the
totals in my gender columns, for each age row:
=SUMPRODUCT(--(MOD(COLUMN(5:5),2)=0),5:5)

But it does not work because it only counts every *other* column, and
what I need to do is count every 12th column. I tried to modify the
above as follows:
MOD(...,12)

But when I checked it, the figure was incorrect. So there must be
something wrong.

Can anyone help?