Insert columns and fill down the formula "SUM"

Panoos64

Well-known Member
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​

<tbody>
</tbody>

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​

<tbody>
</tbody>

Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Col Delane

Active Member
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:

1. 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.)
2. Insert a column between C & D
3. Enter 2019 in D7
4. Enter the following formula in new cell D7:
5. = SUMIFS( \$7:\$7, 8:8, D\$7 )
6. As you're now working with a structured table, the formula should automatically fill down.
7. 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

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

Col Delane

Active Member
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!! :banghead I would recommend the following:

1. you reverse your timeline so that it goes from left to right in time as is usual convention.
2. 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
3. freeze panes (rows & columns) at C8
4. 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
5. 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
6. 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)
7. In D7, enter =Year(D6), format the cell to "General" so that a whole number is displayed, then copy to E7+
8. 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

Well-known Member

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!

Last edited:

Col Delane

Active Member
=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​

<tbody>
</tbody>

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

<tbody>
</tbody>

Panoos64

Well-known Member
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

Replies
1
Views
211
Replies
1
Views
207
Replies
6
Views
229
Replies
3
Views
416
Replies
21
Views
659