Recalculate 'COUNTIF' fields each month

**jnmturner** · 03-06-2008, 07:22 AM

I need a macro to automatically update 50 cells (currently done manually) with the calculation below. The calculation is different in each cell ie a different range is specified but the concept is the same.

COUNTIF(‘SHEET 1’!$A$1:$?$99,”X”)

SHEET 1 remains static
$A$1 remains static for the particular cell - each cell has a different value

$?$ ? moves to next column number each time the macro is run
$?$99 99 remains static - each cell has a different value

“X” remains static - differs from cell to cell

I hope this makes sense. I do not have a great background in Macros thus the request

**Richard Buttrey** · 03-06-2008, 09:59 AM

Originally Posted by jnmturner

I need a macro to automatically update 50 cells (currently done manually) with the calculation below. The calculation is different in each cell ie a different range is specified but the concept is the same.

COUNTIF(‘SHEET 1’!$A$1:$?$99,”X”)

SHEET 1 remains static
$A$1 remains static for the particular cell - each cell has a different value

$?$ ? moves to next column number each time the macro is run
$?$99 99 remains static - each cell has a different value

“X” remains static - differs from cell to cell

I hope this makes sense. I do not have a great background in Macros thus the request

Hi,

To where are you copying this formula? Across a range of columns or down a range of rows.

If the former then I don't see why you need a macro. If you remove the $ sign from the front of the column reference in the second part of the formula, thus making it relative, then the column should adjust in the normal way when you copy it across your 50 cells. i.e.

Please Login or Register  to view this content.

If the latter then you're going to need to introduce an INDIRECT() element to the formula. In which case post back. Either way a macro is not necessary.

Rgds

**jnmturner** · 03-06-2008, 11:04 AM

Hi Richard,

Thanks for your response. To explain a little better:
I have a month/year cell on Sheet 1 which is updated manually each month. Also on Sheet 1 I have a list of columns, with data, for a range of months up to 2009. I need to extend the range in the COUNTIF statement each month to the column that corresonds to the month/year. Instead of changing each of the 50 cells manually I am looking to get a way of automating it once the month/year cell is updated.

e.g.
COUNTIF(‘SHEET 1’!$A$1:$W$99,”X”) Feb 2008 Report
COUNTIF(‘SHEET 1’!$A$1:$X$99,”X”) Mar 2008 Report
COUNTIF(‘SHEET 1’!$A$1:$Y$99,”X”) Apr 2008 Report

this has to be done for 50 cells each of which has a different range (on Sheet 2).

Hope this makes sense.

**Richard Buttrey** · 03-06-2008, 01:11 PM

Originally Posted by jnmturner

Hi Richard,

Thanks for your response. To explain a little better:
I have a month/year cell on Sheet 1 which is updated manually each month. Also on Sheet 1 I have a list of columns, with data, for a range of months up to 2009. I need to extend the range in the COUNTIF statement each month to the column that corresonds to the month/year. Instead of changing each of the 50 cells manually I am looking to get a way of automating it once the month/year cell is updated.

e.g.
COUNTIF(‘SHEET 1’!$A$1:$W$99,”X”) Feb 2008 Report
COUNTIF(‘SHEET 1’!$A$1:$X$99,”X”) Mar 2008 Report
COUNTIF(‘SHEET 1’!$A$1:$Y$99,”X”) Apr 2008 Report

this has to be done for 50 cells each of which has a different range (on Sheet 2).

Hope this makes sense.

Hi,

I'm not quite clear where your 50 columns comes in if you're only projecting through to 2009, but I'll have a stab. This problem resolves itself when we can convert the current month and year into a 'Period' number, and then convert this period number into a column letter reference, which we can then use in an INDIRECT() function.

So first the period.

Instead of the Month/Year cell, or as well as if you need this for some other purpose, have a cell where you enter the first day of the month in question. i.e. a 'proper' XL date number. In the case of say February enter 1/2/08 (That's a standard UK presentation - I guess if you're in the US you do it the wrong way and express it as 2/1/08

) Put this in say A1

Now in another cell which you name 'Period' enter the following formula:

Please Login or Register  to view this content.

This will repesent a column offset depending which month you're dealing with. For instance Feb 2008 returns 2, and Feb 2009 returns 14.

Now you can change your COUNTIF() function to include an INDIRECT() function which uses this period number :

Please Login or Register  to view this content.

Obviously this is based on column W (col 23) being February 2008 and the first of your columns of data. i.e. the period number plus 21. Obviously adjust this if things change.

HTH

**jnmturner** · 03-07-2008, 05:13 AM

Hi Richard,
To explain the 40 (not 50) cell calcs, I have 10 sections for accumulation and each has four calculations, 2 C/p and 2 ytd. I.E Milestone 1 goes from row 12 to 31, Milestone 2 goes from row 32 to 47 etc.

I have a range of dates in different columns and what I need to do is lookup this range with the Inputted date and identify which column the hit is in.

I.E.
Col N Apr 07; Col O May 07; ............; Col AG Nov 08

If the inputted date is for May 07, I need to identify Col 'O' and use this in the COUNTIF formula.

Does this make it any clearer, sorry about the confusion.

**Richard Buttrey** · 03-07-2008, 09:44 AM

Originally Posted by jnmturner

Hi Richard,
To explain the 40 (not 50) cell calcs, I have 10 sections for accumulation and each has four calculations, 2 C/p and 2 ytd. I.E Milestone 1 goes from row 12 to 31, Milestone 2 goes from row 32 to 47 etc.

I have a range of dates in different columns and what I need to do is lookup this range with the Inputted date and identify which column the hit is in.

I.E.
Col N Apr 07; Col O May 07; ............; Col AG Nov 08

If the inputted date is for May 07, I need to identify Col 'O' and use this in the COUNTIF formula.

Does this make it any clearer, sorry about the confusion.

Hi,

I'm not clear whether my last suggestion helped or not. Have you tried it?

Rgds

**jnmturner** · 03-07-2008, 10:33 AM

Hi Richard,

Tried but may not work the way I need. The columns being used are from 'N' to 'AG' and may expand. The dates currently are from Apr 07 to Nov 08 and may roll in the future. This is why I need a formula to check the date inputted against the range of column dates (N:AG currently) and identify the column for it equals. I then need to put the result (Column) into the COUNTIF statement. Again thanks for your help so far and apologies for the confusion.

**Richard Buttrey** · 03-07-2008, 11:55 AM

Originally Posted by jnmturner

Hi Richard,

Tried but may not work the way I need. The columns being used are from 'N' to 'AG' and may expand. The dates currently are from Apr 07 to Nov 08 and may roll in the future. This is why I need a formula to check the date inputted against the range of column dates (N:AG currently) and identify the column for it equals. I then need to put the result (Column) into the COUNTIF statement. Again thanks for your help so far and apologies for the confusion.

Can you zip up the workbook and attach it here so that I can take a look? A few notes on the face of the workbook identifying the cells/formulae you're talking about, and how they relate to other cells/columns/sheets, would be useful.

Rgds

**jnmturner** · 03-07-2008, 02:42 PM

Hi Richard,

Will zip it over the weekend, have to amend it take out some 'live' data.

**jnmturner** · 03-10-2008, 05:02 AM

Hi Richard,

Sorry about the delay but here is the file I am working on at the moment.

**Richard Buttrey** · 03-11-2008, 05:32 AM

Originally Posted by jnmturner

Hi Richard,

Sorry about the delay but here is the file I am working on at the moment.

Hi, Just a quick question re the file you sent.

This appears to be currently set to Feb 2008, i.e. the date in F8 on Calc Sheet1.

Why therefore on calc sheet2 in E15, are you looking at column W on calc sheet 1, which represents January?

I'm puzzled Surely E15 on sheet 2 for February should be column X?

Rgds

**jnmturner** · 03-11-2008, 06:14 AM

The reporting (cut off) by date is 14th Feb but the accumulation is to Jan m.e.
This is a sheet I inherited and that was my first question when it was given to me as well.

**Richard Buttrey** · 03-11-2008, 07:56 AM

Originally Posted by jnmturner

The reporting (cut off) by date is 14th Feb but the accumulation is to Jan m.e.
This is a sheet I inherited and that was my first question when it was given to me as well.

Hi,

I'm attaching the updated workbook and put the relevant formulae in E15 and G15 on Calc sheet2. You'll need to copy them to all the other alternate rows.

On Calc sheet 1 you'll note that I've added a new column AL which contains the text of the '1 Milestone', '2 Milestone' ...etc. Without this, the formula I've used would have been even longer since the dual lines on calc sheet 1 in column C only contain the milestone reference on the first of each of the two A & P rows, and it would need to have worked it out within the formula. I felt it was better to do this than make the formula even more complicated.

You didn't mention the cumulative columns I:L but I've assumed you need these to update in the same way and have entered formulae in I15 & K15 which you'll need to copy down.

Regards