Using a number as a reference to add a column

[jdhfch]

HI,

I have a list of data as:

[TABLE="width: 100"]
<colgroup><col width="64" style="width: 48pt;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]January[/TD]
[TD="width: 69, bgcolor: transparent"] 473 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]February[/TD]
[TD="bgcolor: transparent"] 399 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]March[/TD]
[TD="bgcolor: transparent"] 706 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]April[/TD]
[TD="bgcolor: transparent"] 658 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]May[/TD]
[TD="bgcolor: transparent"] 527 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]June[/TD]
[TD="bgcolor: transparent"] 733 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]July[/TD]
[TD="bgcolor: transparent"] 691 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]August[/TD]
[TD="bgcolor: transparent"] 726 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]September[/TD]
[TD="bgcolor: transparent"] 635 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]October[/TD]
[TD="bgcolor: transparent"] 884 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]November[/TD]
[TD="bgcolor: transparent"] 1,049
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]December[/TD]
[TD="bgcolor: transparent"] 352
[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 100"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]January
[/TD]
[TD="width: 69, bgcolor: transparent"] 473
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]February
[/TD]
[TD="bgcolor: transparent"] 399
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]March
[/TD]
[TD="bgcolor: transparent"] 706
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]April
[/TD]
[TD="bgcolor: transparent"] 658
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]May
[/TD]
[TD="bgcolor: transparent"] 527
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]June
[/TD]
[TD="bgcolor: transparent"] 733
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]July
[/TD]
[TD="bgcolor: transparent"] 691
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]August
[/TD]
[TD="bgcolor: transparent"] 726
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]September
[/TD]
[TD="bgcolor: transparent"] 635
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]October
[/TD]
[TD="bgcolor: transparent"] 884
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]November
[/TD]
[TD="bgcolor: transparent"] 1,049
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]December
[/TD]
[TD="bgcolor: transparent"] 352
[/TD]
[/TR]
</tbody>[/TABLE]

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))