Count cells every 12th column?

[tours]

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?

 

[Bernie Deitrick]

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

 

[tours]

Thank you, that works!! The averages formula doesn't, but I think I
can work around that another way. (I know how many Saturdays are in a
season, so I can just do a formula based on the return result from the
total visitation per day.

 

[Bernie Deitrick]

The average formula will work if the cells without values are blanks, rather than filled with
zeroes.

HTH,
Bernie
MS Excel MVP