Using a number as a reference to add a column

jdhfch

New Member
Joined
Jan 25, 2018
Messages
24
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,858
Office Version
  1. 2019
Platform
  1. Windows
Assume your data is in columns A & B. In C1, type =SUM($B$1:B1) and copy down.
 

jdhfch

New Member
Joined
Jan 25, 2018
Messages
24
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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

New Member
Joined
Jan 25, 2018
Messages
24
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
=SUMPRODUCT((TODAY()>=(TEXT("01/"&A1:A12&"/"&YEAR(TODAY()),"dd/mm/yy")+0))*(B1:B12))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,227
Messages
5,527,539
Members
409,769
Latest member
Hudz

This Week's Hot Topics

Top