Using a number as a reference to add a column

[jdhfch]

HI,

I have a list of data as:

January	473
February	399
March	706
April	658
May	527
June	733
July	691
August	726
September	635
October	884
November	1,049
December	352

<colgroup><col width="64" style="width: 48pt;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <tbody> </tbody>

I want to add the rows based on the month.eg in Feb, I want to add Ja & Feb (row 1 & 2) etc. I will extract the row number from the date, I just don't know how to construct the formula?

Please can anyone help
Rgds

jdhfch

 

[alansidman]

Assume your data is in columns A & B. In C1, type =SUM($B$1:B1) and copy down.

 

[jdhfch]

Sorry, I meant to do this dynamically without adding any columns. for instance, In April, I want it to add four rows, in June 6 rows etc. This would be by me extracting the month (4 for April) and then adding 4 rows up, or 6 for Jun etc?

Rgs

Jason

HI,

I have a list of data as:

January	473
February	399
March	706
April	658
May	527
June	733
July	691
August	726
September	635
October	884
November	1,049
December	352

<tbody>
</tbody>

I want to add the rows based on the month.eg in Feb, I want to add Ja & Feb (row 1 & 2) etc. I will extract the row number from the date, I just don't know how to construct the formula?

Please can anyone help
Rgds

jdhfch

 

[Special-K99]

Are these month names or are these Excel dates formatted to display the month only?

You might want to add a year as well.
What happens when December goes into January? You'll have 2 Januarys in that list.

Maybe you should use a SUMIF comparing the date against today's date?

 

[jdhfch]

Hi

I can't use the date as I only use the name of the months and there is only ever 1 year in the data. I don't know how to use a sumif for this. I can get it to extract 1 month, just not to add the relevant months together? This is why I thought that if I had a 6 for June, then the formula would add Ja, Feb, Mar,Apr, May & June (6 rows) etc?

Rgds

jdhfch

 

[Special-K99]

=SUMPRODUCT((TODAY()>=(TEXT("01/"&A1:A12&"/"&YEAR(TODAY()),"dd/mm/yy")+0))*(B1:B12))