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

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.

**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!

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 MoreDo you need to know how many precedents or dependents there are on a worksheet? You could count them manually, or you ...

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

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

2023-12-26 10:29:27

J. Woolley

=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

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

• =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

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

=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

=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

=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.

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.

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

Copyright © 2024 Sharon Parq Associates, Inc.

## Comments