Real Challenge!! Sum every fifth cell in a column

**jwleonard** · 03-21-2005, 02:53 PM

I have an elaborate workbook with a summary page, I need to find a way to sum every fifth cell (sometimes sixth) in particular columns. Please see the attached screen shot for a better idea of what I am trying to accomplish. Obviously there is more data than you can see in the picture but it will give you the idea of the pattern used. The sum of these cells will go at the bottom of the column and must have the ability to move down when more data is added. Would I be able to use a dynamic named range to do this, will it have to be VBA, or is it not possible? I can't change the layout of the data due to the other sheets in the workbook. It also needs to automatically update if another course is added (grouping of five rows). Please help, I have countless hours in this product and this is all that is stoping it from being perfect.

**swatsp0p** · 03-21-2005, 04:06 PM

Hi, JW. Based on your sample, can I assume the data you want to total is in the Row containing "Aug" in column G?

With that assumption, SUMIF will do what you ask:

=SUMIF(G13:G1000,"Aug",J12:J1000)

will add together all cells in J12:J1000 that have 'Aug' in corresponding row's Column G

Of course, adjust ranges to suit your need.

HTH

Bruce

**jwleonard** · 03-21-2005, 06:49 PM

Bruce, thanks for the reply but that isn't what I am looking to do. I need to add all the backlog numbers for each column and get a total at the very bottom. Then I need to add all the numbers for May and get a total at the bottom, then June.... The problem is the months change each time a new month comes around, it will always be the next four months. Thanks again

**swatsp0p** · 03-22-2005, 11:39 AM

JW, there is a way to make my formula work for you. As the Months will change; instead of listing the month's name (e.g. "Aug"), we only need to reference a cell that contains the month. In your example, the "Key" in rows 8-12 will do the trick. Thus the formula for Backlog in column 'I' would look something like this:

=SUMIF($G$13:$G$1000,$G$8,I13:I100)
to understand this formula, the first reference is where the qualifying values (e.g. "Backlog") are; the $G$8 is where the value to find is stored; the final reference is the range to be summed. Therefore, for each G8 found in G13:G1000, add the corresponding value in I13:I1000.

May would be:

=SUMIF($G$13:$G$1000,$G$9,I13:I100)

Then when you change May to June (or whatever), the formula will sum the values equal to G9.

Repeat for the remaining months, and copy formula to the right for the other columns. Formulas should adjust automatically to match the column it is in. (note the '$') for the absolute reference to the 'fixed' values to search and find.

HTH

Bruce

**jwleonard** · 03-22-2005, 02:08 PM

That works great, thanks!!
I must say that I am dissapointed in myself for not thinking of that. I had considered a sumif but didn't notice that I had any kind of dependable criteria to use it with. The data in the G8-G12 blocks was put in as filler and left by mistake, so it is kind of funny that it wound up having a use. Thanks again!

Jeff

**swatsp0p** · 03-22-2005, 03:12 PM

I am glad this worked for you. Thanks for the feedback.

Remember, "For everything, there is a purpose...."

Cheers,

Bruce

Real Challenge!! Sum every fifth cell in a column

LinkBack

Thread Tools

Rate This Thread

Display

Real Challenge!! Sum every fifth cell in a column

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions