Putting Amounts in Month Columns

Written by Allen Wyatt (last updated December 23, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


8

Tonja has a worksheet that uses 14 columns. Column A is a date, column B is a dollar figure, and columns C:N are months (January through December). She needs to fill C2:N2 with a formula that will, effectively, show the amount from column B in the proper month based on the date in column A.

How you put this formula together depends on how you designate the months in your data. Since you need to start filling out formulas in row 2 (you said C2:N2), then we can assume that you have column headings in row 1. The "date" and "amount" column headings are easy to figure out; it is the month column headings that can be handled in different ways.

As an example, let's assume that your headings for months are actually dates. For instance, in C1 you could enter a date such as 1/1, and then put 2/1 in D1, 3/1 in E1, etc. Excel parses these as dates in the current year. You can then format these cells using a custom format (either "mmm" or "mmmm") so that only the month name is displayed.

If this is how you put your headings together, then you could enter the following formula into cell C2:

=IF(MONTH($A2)=MONTH(C$1),$B2,0)

This compares the month in the date column with the month in the heading. If they are the same, then the value in B2 is returned; if not, then 0 is returned. You can copy this formula to the right and down as many rows as necessary.

Key to making this formula work is the strategic placement of the dollar signs in the cell references. $A2 means that the column reference won't change when the formula is copied, but the row reference can change. Similarly, C$1 means that the column reference can change, but the row reference will not change. Thus, we are always comparing the proper date with the proper month and pulling the dollar figure from the proper column.

Once this formula is in C2, you can then copy it to the rest of your month cells. Note that the formula always returns a value—either the amount in column B or a zero. You can configure Excel to hide zero values, which may make your data more readable, or you can change the formula slightly in this way:

=IF(MONTH($A2)=MONTH(C$1),$B2,"")

Now, back to your heading data. It is very possible that you "hard coded" your months in cells C1:N1, meaning you entered month names as text. If this is the case, then you can still use a formula, but you'll need to rely on column positioning. Here's the example of what you would enter in C2:

=IF(MONTH($A2)=(COLUMN(C$1)-2),$B2,0)

Notice, in this case, that the date in A2 is being compared to the current column minus 2. Since the COLUMN function will return the value 3 for column C, subtracting 2 from that results in 1, which is the month number for that column. Other than how the column's month is being determined, there is no difference between this formula and the earlier one. It can now be copied to the rest of the columns for the months, and it will work just fine.

It should be noted that Excel always provides multiple paths to the same solution. This is especially true when it comes to formulas. This tip has focused on a couple of simple formulas to accomplish Tonja's issue, but it is entirely possible to come up with other formulaic approaches, as well.

Which one is best? Why, the one that provides the answer you need, of course. In other cases—again, depending on your data—it may be more appropriate to use a different approach, such as relying on a PivotTable or to employ Power Query. For the simple needs expressed by Tonja, however, it appears the formulaic approach may be the most direct approach.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8476) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Toggling Print Preview

Need to easily switch back and forth between print preview and your document? Here's a quick shortcut key you can use to ...

Discover More

Reversed Bolding

If you paste information from one document into another, you may be surprised at the results. If your text changes from ...

Discover More

Copying Found Items to a New Document

Word allows you to use its searching capabilities to easily find multiple items in a document. What if you want to copy ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Summing Absolute Values

You can easily sum a series of values in Excel, but it is not so easy to sum the absolute values of each value in a ...

Discover More

Counting Precedents and Dependents

Do you need to know how many precedents or dependents there are on a worksheet? You could count them manually, or you ...

Discover More

Returning the Minimum of Integers of a Range

If you have a range of numbers that contain both integers and decimal numbers, you may have a need to determine the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine minus 5?

2023-12-26 10:29:27

J. Woolley

Chris Constantinou's excellent formula for cell C2 was
    =IF(MONTH($A2)=COLUMN(A$1),$B2,0)
Here is a simpler version
    =(MONTH($A2)=COLUMN(A$1))*$B2
This works because Excel converts TRUE/FALSE to 1/0 when doing arithmetic. (Note the numeric value of True in VBA is -1, not 1.)
My recent comment below described this array formula inspired by Chris
    =IF(MONTH($A2)=COLUMN($A:$L),$B2,0)
Here is the simpler version of this array formula
    =(MONTH($A2)=COLUMN($A:$L))*$B2
In newer versions of Excel this formula will automatically spill from cell C2 as a dynamic array in cells C2:N2. The array formula in cell C2 can be copied to fill additional rows below row 2.
In older Excel versions it is necessary to select cells C2:N2 before entering the formula with Ctrl+Shift+Enter, which makes it a CSE array. The CSE array formula in C2:N2 can then be copied to fill additional rows.


2023-12-26 09:52:48

J. Woolley

@Steve Jez
I appreciate your comment, but many of us consider discussions like this as "fun with Excel" because there are so mnay different ways to achieve a result.


2023-12-26 09:26:01

Steve Jez

I appreciate the original post asked for a formulaic approach, but, without wishing to sound harsh or rude, I do wonder if sometimes if a general lack excel knowledge or understanding has prompted the question. In that, this is the way I currently do it manually & I'd like to replace it with a formula. And it's great that people pile in with answers & then improvements on those answers as already shown in this thread.
However, on the brief information we're given here, I can think of no logical reason to split the transactions into monthly columns other than to be able to sum the months for monthly totals.

Based on this example I would enter the initial data - Date Amount in a table, with one additional column for the month calculation. Then use a summarising formula to provide a month by month running total. This way you end up with far fewer formulas to calculate every time a new entry is made.

Summarising formulas in case the image is not clear, the first 2 are required regardless of which of the summarising formulas are used - Filter - Sumifs - Sum

Column A =MONTH([@Date])

Column E =TEXT(E3*29,"MMM")

Column G =SUM(FILTER(Table1[Amount],MONTH(Table1[Date])=E3,0))
Column H =COUNT(FILTER(Table1[Amount],MONTH(Table1[Date])=E3,""))

Column J =SUMIFS(Table1[Amount],Table1[Month],E3)
Column K =COUNTIFS(Table1[Month],E3)

Column M =SUM((MONTH(Table1[Date])=E3)*Table1[Amount])

If months & years were the criteria, it would be a pretty simple amendment.

I hope I haven't missed the point & this post is only intended to help.
(see Figure 1 below)

Figure 1. General principal


2023-12-25 07:48:23

Alex Blakenburg

For text months in the heading rather than relying on column position you could do something like this:
• =IF(TEXT($A2,"mmm")=C$1,$B2,0) <--- for Jan, Feb etc
•=IF(TEXT($A2,"mmmm")=C$1,$B2,0) <--- for January, February etc
or still working with text months if you have MS365 using the spill function with the formula in C2
• =IF(MONTH($A$2:$A$16)=MONTH("01-"&$C$1:$N$1),$B$2:$B$16,0)
•=IF(TEXT($A$2:$A$16,"mmm")=$C$1:$N$1,$B$2:$B$16,0) <--- for Jan, Feb etc


2023-12-24 10:26:59

Willy Vanhaelen

Here is another approach, without any formulas in the sheet, by using this tiny event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If IsDate(Target.Offset(0, -1)) = False Then Exit Sub
Cells(Target.Row, Month(Target.Offset(0, -1)) + 2) = Target
End Sub

To use it, you must first enter the date then the amount. This amount is then automatically copied in the right month column.

If you want to change a row, first delete the amount. The corresponding month amount will also be deleted. Then you can enter a new date and corresponding amount.

The header can be a traditional one with any words of your choice.

You can use any row in the column range A:N without any need to copy formulas.

The columns not used in a row stay empty so the file will be much smaller in size.

Remember to put his macro in the sheet’s code page. Right click the sheet tab an select “View Code”. That’s the place to be.


2023-12-24 09:36:15

J. Woolley

My initial comment below discusses the following array formula
    =IF(MONTH($A2)=(COLUMN($C:$N)-2),$B2,0)
As suggested by Chris Constantinou, here is a simpler version
    =IF(MONTH($A2)=COLUMN($A:$L),$B2,0)


2023-12-24 05:17:41

Chris Constantinou

My contribution to this tip was the following formula in C2
=IF(MONTH($A2)=COLUMN(A$1),$B2,0)
which eliminates the need to subtract 2 from the COLUMN function result.


2023-12-23 11:25:13

J. Woolley

The Tip's last formula intended for cell C2 is
    =IF(MONTH($A2)=(COLUMN(C$1)-2),$B2,0)
This can be simplified as follows
    =IF(MONTH($A2)=(COLUMN()-2),$B2,0)
The advantage of both is their independence of the contents of header row 1. After entering either formula in cell C2, it can be copied to columns C:N in rows below row 1.
Here is an equivalent array formula for cell C2
    =IF(MONTH($A2)=(COLUMN($C:$N)-2),$B2,0)
In newer versions of Excel this formula will automatically spill from cell C2 as a dynamic array in cells C2:N2. The array formula in cell C2 can be copied to fill additional rows below row 2.
In older Excel versions it is necessary to select cells C2:N2 before entering the formula with Ctrl+Shift+Enter, which makes it a CSE array. The CSE array formula in C2:N2 can then be copied to fill additional rows.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.