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.

Other info: We are only open Monday - Saturday, so I only have those
dates across the top. Also, below each day there are two sub-columns-
Male and Female.

I need to add up all the visitor totals for each day so I can get an
average number of visitors on Mondays, Wednesdays, Saturdays, etc.

I have tried to modify this formula which I've used to count the
totals in every other column:
=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.

Can anyone help?

Here is an attempt to show you what the spreadsheet looks like:

6/1 6/2
Male Female
Male Female
Under 18 1 0
3 5
18-34 2 4
5 2
35-63 10 15
4 2
63 and up 11 11
6 12

Total per day 54 39

I am looking to make a formula which would deal with the "total per
day" row. I need to have it count every 12th cell & be able to give it
a different starting point for each day calculation, because this
would allow it to count up all the Saturday totals, all the Monday
totals, etc, etc, etc.

I have no idea what your data looks like or why you need every 12th, but
your formula gets every other because it uses MOD(..., 2), you need MOD)...,
12)

--
__________________________________
HTH

Bob

wrote in message
...
Here is an attempt to show you what the spreadsheet looks like:

6/1 6/2
Male Female
Male Female
Under 18 1 0
3 5
18-34 2 4
5 2
35-63 10 15
4 2
63 and up 11 11
6 12

Total per day 54 39

I am looking to make a formula which would deal with the "total per
day" row. I need to have it count every 12th cell & be able to give it
a different starting point for each day calculation, because this
would allow it to count up all the Saturday totals, all the Monday
totals, etc, etc, etc.

Sorry, the spreadsheet description post didn't work very well.

I have tried the MOD(...,12) and when I checked it, the figure was
incorrect. So there must be something wrong.

Let me try a screen shot:

<a href="http://www.flickr.com/photos/jenkob/2628527135/"
title="screen"<img src="http://farm4.static.flickr.com/
3074/2628527135_911b2e2e82_b.jpg" width="1024" height="640"
alt="screen" /</a

Well I see the data okay now, but have no idea what you are trying to count.
Your so-called age labels are One, Two, Three, Four. Is One 17 and under,
Two 18-34, etc.?

Are you just trying to sum all male visitors for One, Two, etc.? If so, just
use

=SUMIF($4:$4,"M",5:5)

=SUMIF($4:$4,"F",5:5)

and so on

--
__________________________________
HTH

Bob

wrote in message
...
Sorry, the spreadsheet description post didn't work very well.

I have tried the MOD(...,12) and when I checked it, the figure was
incorrect. So there must be something wrong.

Let me try a screen shot:

<a href="http://www.flickr.com/photos/jenkob/2628527135/"
title="screen"<img src="http://farm4.static.flickr.com/
3074/2628527135_911b2e2e82_b.jpg" width="1024" height="640"
alt="screen" /</a

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....... my ultimate end goal is to be able to get an average number
of visitors for each day of the week.

On Jul 1, 4:38 pm, "Bob Phillips" wrote:
Well I see the data okay now, but have no idea what you are trying to count.
Your so-called age labels are One, Two, Three, Four. Is One 17 and under,
Two 18-34, etc.?

Are you just trying to sum all male visitors for One, Two, etc.? If so, just
use

=SUMIF($4:$4,"M",5:5)

=SUMIF($4:$4,"F",5:5)

and so on

--
__________________________________
HTH

Bob

wrote in message

...

Sorry, the spreadsheet description post didn't work very well.

I have tried the MOD(...,12) and when I checked it, the figure was
incorrect. So there must be something wrong.

Let me try a screen shot:

<a href="http://www.flickr.com/photos/jenkob/2628527135/"
title="screen"<img src="http://farm4.static.flickr.com/
3074/2628527135_911b2e2e82_b.jpg" width="1024" height="640"
alt="screen" /</a

This was resolved in a cross-post on the programming forum:
http://groups.google.com/group/micro...4503966a08c04#

Thanks for the ideas!