Re: Count cells every 12th column?

[@consumerdotorg 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

<to...@aldoleopold.org> wrote in message
news:d60726bf-ce79-4266...@26g2000hsk.googlegroups.com...
> 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?

[to...@aldoleopold.org]

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.

[@consumerdotorg Bernie Deitrick]

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

HTH,
Bernie
MS Excel MVP

<to...@aldoleopold.org> wrote in message
news:00bf57f7-a4e3-4d51...@e53g2000hsa.googlegroups.com...