Insert columns and fill down the formula "SUM"

Panoos64 · Mar 4, 2019

Hi all, I would like to use a VBA code, so that, to insert columns next to years 2018 & 2019 and fill down the formula to “SUM” each year separately. Therefore, that the columns are not stably. I present below the original data and the expected result. Thank you all in advance

Original data

	A	B	C	D	E	F	G	H	I
6	DEBT. CODE	DESCRIPTION	GRAND	March	February	January	December	November	October
7			TOTAL	2019	2019	2019	2018	2018	2018
8	DEB 5050101	DEBTOR 1	2,356	125	455	150	478	725	423
9	DEB 5050102	DEBTOR 2	2,613	220	1,240	245	355	405	148
10	DEB 5050103	DEBTOR 3	10,837	479	7,145	658	855	955	745

Expected result

	A	B	C	D	E	F	G	H	I	J	K
6	DEBT. CODE	DESCRIPTION	GRAND	TOTAL	March	February	January	TOTAL	December	November	October
7			TOTAL	2019	2019	2019	2019	2018	2018	2018	2018
8	DEB 5050101	DEBTOR 1	2,356	730	125	455	150	1,626	478	725	423
9	DEB 5050102	DEBTOR 2	2,613	1,705	220	1,240	245	908	355	405	148
10	DEB 5050103	DEBTOR 3	10,837	8,282	479	7,145	658	2,555	855	955	745

Col Delane · Mar 5, 2019

Is the Original Data table you've shown the extent of the table, or is there more? If so, how much?

Depending on your answers to the above, there is a very simple non-VBA solution:

If not done already, convert your data table to an Excel Table (click anywhere inside the table and press Ctrl+T, confirm the dimensions and click Ok.)
Insert a column between C & D
Enter 2019 in D7
Enter the following formula in new cell D7:
= SUMIFS( $7:$7, 8:8, D$7 )
As you're now working with a structured table, the formula should automatically fill down.
inserting a column for the 2018 Total between January and December, and then repeat steps 3-5

An alternative to the above is to locate these year Totals (together side-by-side) somewhere else so you're not intervening in the continuous timeline in row 6.

Panoos64 · Mar 8, 2019

I am sorry for my delay respond. I will follow your instructions and i will let you know. Thank for your support

Col Delane · Mar 8, 2019

If not already part of your spreadsheet table, and only if you don't use an Excel table as I suggested (because Excel won't let you have field headers in a table that are created by formula!!

) I would recommend the following:

you reverse your timeline so that it goes from left to right in time as is usual convention.
use Grouping to group columns of the same year, thus allowing you to collapse the prior years to display the periods you want next to the "frozen" columns of A:B
freeze panes (rows & columns) at C8
enter the date (in mm/dd/yy format if you use that vs dd/mm/yy which we do down here in Australia) of the very first month in D6
in E6 use either of the following, then copy this formula to F6+
1. =EDATE( D6, 1) if your start date is 1st of the month, or
2. =EOMONTH(D6, 1) if your start date is last day of the month
format the date-values in row 6 to "Mmmm" or "Mmm" so that the month name is displayed (but the cell retains the date number underneath to allow you to perform date math)
In D7, enter =Year(D6), format the cell to "General" so that a whole number is displayed, then copy to E7+
If you follow my first method in post #2 , you need to link the cell in row 6 in which your year total will be to the cell immediately to the left (For example, Dec 2018 is in column Q, R6 = Q6) then format that cell to "Yyyy" so that it displays the year but retains the datevalue, for R7 = Year(R6) (as per step 7 above)

This makes extending your timeline a bit easier.

Panoos64 · Mar 12, 2019

Hi Col, i express my thanks to you for your instructions and your time spent for my project. Unfortunately is so complicate for me to follow the above, but what i got is the formula to "SUM" each year separately which is follow, =SUMIF($F$7:$M$7,"*2018*",F8:M8) for year 2018 and for year 2019 is, =SUMIF($F$7:$M$7,"*2019*",F8:M8).
I created two new columns between "C" & "D" and i entered the above formulas, coping them in rows down. Therefore that i did not use "Table"
However using your suggested formula (point 5. on your first post) and changing a bit e.g. "SUMIF" instead of "SUMIFS", i resolved my issue. I wanted to insert automatically the new columns, in adjacent cell next to each year, but at the end is not a matter. I just decided to have my results in other layout by which i have the expected results too.
Thank you once again for your support. It was so kind by you. Hv a great day!

Col Delane · Mar 12, 2019

Panoos64 said:
=SUMIF($F$7:$M$7,"*2018*",F8:M8) for year 2018 and for year 2019 is, =SUMIF($F$7:$M$7,"*2019*",F8:M8)

Ooops!

Sorry, my mistake with the formula. I would recommend SUMIFS function vs the older (and really now obsolete) SUMIF as you can have multiple criteria (1+) with SUMIFS but only one with SUMIF. My mistake was to have the arguments in the wrong place.

The syntax for SUMIF was the other opposite of SUMIFS
=SUMIF( Criteria_range, Criteria, Sum_range )

Because of the multiple criteria, the Sum_range for SUMIFS had to be put first:
=SUMIFS( Sum_range, Criteria_range1, Criteria1, [Criteria_range2, Criteria2], ...)
which in your case would be:
=SUMIFS( $F8:$M8, $F$7:$M$7, "*2018*" )

I would also recommend replacing your text criteria ( "*2018*" ) with a formula linking to the cell in your Annual Total columns that holds the year value (input or via formula) for the year you're summing (as this saves having to edit your formulas)

Here is the layout I was envisaging:

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]K[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]	DEBT. CODE	DESCRIPTION	Month >>	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	\|	Total	Total	Grand Total
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]			Year >>	2018	2018	2018	2019	2019	2019	\|	2018	2019
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]	DEB 5050101	DEBTOR 1	$	423	725	478	150	455	125	\|	1,626	730	2,356
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]	DEB 5050102	DEBTOR 2	$	148	405	355	245	1,240	220	\|	908	1,705	2,613
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]	DEB 5050103	DEBTOR 3	$	74	955	855	658	7,145	479	\|	1,884	8,282	10,166

Formulas:

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]K[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]	DEBT. CODE	DESCRIPTION	Month >>	Oct-18	= EOMONTH( D6, 1 )	= EOMONTH( E6, 1 )	= EOMONTH( F6, 1 )	= EOMONTH( G6, 1 )	= EOMONTH( H6, 1 )	\|	Total	Total	Grand Total
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]			Year >>	=YEAR( D6 )	=YEAR( E6 )	=YEAR( F6 )	=YEAR( G6 )	=YEAR( H6 )	=YEAR( I6 )	\|	2018	2019
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]	DEB 5050101	DEBTOR 1	$	423	725	478	150	455	125	\|	= SUMIFS( $D8:$J8, $D$7:$J$7, K$7 )	= SUMIFS( $D8:$J8, $D$7:$J$7, L$7 )	=SUM( $J8:OFFSET( $M8, 0,-1) )

Panoos64 · Mar 13, 2019

Hi Col, following your above instructions, eventually i finished my project and i am getting now the right results. It was a bit hard for me because some formulas and functions was new for me, like "EDATE" and "EOMONTH" but i was attempting and testing for several times so that to verify that i was getting the right figures. I appreciated so much that you worked for me and due to you i learned some new functions, which i categorized as advance. Furthermore i get the accurate results and in immediately time. I say to you great thanks and all the best. Hv a great, lovely day

Insert columns and fill down the formula "SUM"

Panoos64

Well-known Member

Excel Facts

Col Delane

Active Member

Panoos64

Well-known Member

Col Delane

Active Member

Panoos64

Well-known Member

Col Delane

Active Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

Insert columns and fill down the formula "SUM"

Panoos64

Well-known Member

Excel Facts

Col Delane

Active Member

Panoos64

Well-known Member

Col Delane

Active Member

Panoos64

Well-known Member

Col Delane

Active Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock