# Filtering for Purchases within a Given Month

by Allen Wyatt
(last updated April 9, 2020)

Anand has a worksheet that contains sales information consisting of customer names (column A) and purchase date (column B). He needs to filter the list so that he can see customers who purchased exactly X times within a given calendar month, where X is a value such as 1, 2, etc. He wonders if there is a way to do this using the built-in filtering or is another approach better.

There are actually several different ways you can approach this problem. Excel provides a number of different tools that could be used to derive a result, but I'll only focus on a couple of them. Both approaches require helper columns to use in your filtering.

The first approach uses just a single helper column. Assuming your data begins in row 2 and occupies no more than 99 rows, enter the following in cell C2:

```=COUNTIFS(\$A\$2:\$A\$100,A2,\$B\$2:\$B\$100,">="&
DATE(YEAR(B2),MONTH(B2),1),\$B\$2:\$B\$100,"<"&
DATE(YEAR(B2),MONTH(B2)+1,1))
```

This is a single formula; it returns a count of how many purchases the person in column A made within the month indicated by the date in column B. Copy the formula down as many rows as necessary for your data. With a cell in your data selected, display the Data tab of the ribbon and click the Filter tool in the Sort & Filter group. Excel displays the filter controls at the top of each column in your data.

Click the control at the top of the Date column to display the filtering palette, then pick the month you want to target. (In the years area at the bottom of the palette, clear the Select All check box, then select a year, and finally a month.) Click the control at the top of the helper column to pick how many sales you want in the filtered list. (See Figure 1.)

Note that the result shows multiple occurrences of each name for the target month. Thus, if you choose to display those that have purchased 3 times in a given month, you'll see 3 rows for any given name in the target month.

This is OK for a relatively low X rate for a month. However, if you want to see those who have purchased 50 times in a month, it quickly becomes unwieldy. Instead, you need a way to limit to a single instance of a given name within the filtered list.

This brings us to a solution that requires two helper columns, which I'll call Count and Tally. If your first cell in the Count column is C4, enter this into that cell:

```=IF(EOMONTH(B4,0)<>EOMONTH(\$B\$1,0),0,IF(A4=A3,C3+1,1))
```

Copy this formula down as far as necessary for your data. It returns a value of 0 unless the month and year is a match to the month and year in cell B1. (The date in cell B1 should be within your target month. It can be any date within that month.) If the month and year are a match, then the count increments for each sale for the same customer within that month.

If the first cell of the Tally column is cell D4, then place this formula in that cell:

```=IF(C4>=C5,C4,0)
```

Copy this formula down the column, as well. The formula retains only the highest value from the Count column for any customer, setting all other values to 0.

Now, with any cell in your data selected, display the Data tab of the ribbon and click the Filter tool. Excel displays the filter controls at the top of each column in your data. (See Figure 2.)

Figure 2. An example set of data.

You are now ready to get the desired information. Just sort the data first by column A (the Name column) and then by column B (the Date column). You can then use the drop-down filter control at the top of column D (the Tally column) to select how many sales you want for the desired month (which, remember, is specified in cell B1). If you specify that only the value 1 should be displayed, then you have the names of all the people who purchased just once in the month. If you select 2, then the displayed names are those who purchased twice, etc.

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

##### 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

Want to see the absolute most of your document that you can? Then you need to become familiar with the full-screen ...

Discover More

Putting More than One Hyperlink in a Cell

Excel allows you to put a single hyperlink in a cell. If you have a need to put multiple hyperlinks in a cell, then you ...

Discover More

The Quick Access Toolbar is where you put all the tools to which you need quick access. (Makes sense, huh?) There are ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

##### More ExcelTips (ribbon)

Filtering to a Date Range in the Past

If you have a large number of data records, each with an associated date, you might want to filter that data so you see ...

Discover More

Using a Filtered Value in a Formula

Accessing filtering criteria for use in a formula can be a real need for some worksheet designs. Getting to that ...

Discover More

Displaying Multiple Filtered Colors

The filtering capabilities of Excel are quite helpful in taming a large amount of data. While you can filter by color, ...

Discover More
##### Subscribe

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

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}] 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 five minus 3?

2015-07-05 15:50:55

Yvan Loranger

better late than never?
Assuming names are in A2:A100 and purchase dates in B2:B100
and headers "Client" and "Date" in A1 and B1, not necessary but
sort [respecting presence of col headers] cols A & B by A,
then copy col A into C, use the Remove Duplicates tool on C
[again respect presence of col headers].
Enter # of times in C1, year in D1, month in E1. Duplicate into col D this formula (indented for clarity, should be entered as 1 line):
=COUNTIFS(\$A\$2:\$A\$100, \$C2,
\$B\$2:\$B\$100, ">="&DATE(\$D\$1,\$E\$1,1),
\$B\$2:\$B\$100, "<" &DATE(\$D\$1,\$E\$1+1,1)
)
This will show the number of purchases for the month for each client.
If you prefer to highlight the customers who purchased C1 times then duplicate into col D the following formula (again indented):
=IF(COUNTIFS(\$A\$2:\$A\$100, \$C2,
\$B\$2:\$B\$100, ">="&DATE(\$D\$1,\$E\$1,1),
\$B\$2:\$B\$100, "<" &DATE(\$D\$1,\$E\$1+1,1)
) =\$C\$1,
TEXT(\$C\$1,"#0")&" purchases",
"no"
)

2015-06-08 08:28:09

Beverly Gilmore

Hello Allen,

I wonder if there is a tool in excel that acts like a ruler? Often I have my source data on one screen and attempt to input into another screen but as my eyes shift from one screen to the other, it is hard to keep track of where you left off? Any ideas?

2015-06-06 09:12:15

John Hamm

thank you

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