I have a table of data that has my customers entered in Column A. Row 1 will have 4 columns of data per each customer for each business day of the month. For example: customer A2 will have columns B2:E2 holding data for 11/1/2010, F2:I2 for 11/2/2010 and so on. Each day captures the same type of data. For example: B2, F2 will hold daily sales #s, C2, G2 will hold the corresponding sales $ and so on. What I am looking for is a formula that will allow me to get a monthly total of column of data/per day for each customer. Again, each day holds 4 unique columns of data for that day for each customer. I want monthly totals per customer of each of the 4 unique daily columns of data. I have figured out how to add every 4th colunm of the table but am lost beyond that.
Thanks in advance. I am using Excel 2007.
How to add every nth column in a table



Re: How to add every nth column in a table
Hi
Can you please post a sample spreadsheet explaining what you are looking for.
Regards
Maqbool

Re: How to add every nth column in a table
forum.ozgrid.com/index.php?attachment/34409/Here is a sample. I am looking for an efficient way to find the monthly summary numbers. Assume we have a full table of about 22 monthly business days and I have about 400 customers. So, doing this manually is a bit of work.

Re: How to add every nth column in a table
S3: =SUMPRODUCT(($B$2:$Q$2=S$2)*($B3:$Q3))
You can then Autofill that across your 4 columns and then down as far as required. It requires the data header columns on the left to exactly match the ones in your monthly summary.
As a side note I would recommend that you do not use Merge and Center for your date headings. Instead repeat the date for each column. Later if you wish to try to analyse for a specific date you will not be able to do so easily with the date headings the way they are. If you want a neat 'heading' then construct that in a separate row, you can always hide the row with the multiple dates.
Try entering the following two formula in any cell and you will see an illustration of the problem:
=B1
=C1
The first returns your date from B1 (1Oct)
The second returns 0
If you want to try to look up products against dates then using Merge and Centre in this way makes it hugely difficult. 
Re: How to add every nth column in a table
Thank youthis worked perfectly. And thanks for the side note as well. I was not aware of the trouble that might cause.

Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!