Filtering for Purchases within a Given Month

by Allen Wyatt
(last updated June 6, 2015)

3

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 purchase 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. 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.)

Figure 1. Your filtered data.

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, and 2013.

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

Stopping Date Parsing when Opening a CSV File

Excel tries to make sense out of any data that you import from a non-Excel file. Sometimes this can have unwanted results. ...

Discover More

Associating a Name with a Position

Wouldn't it be great if Word allowed you to have a small pop-up that showed you some information associated with a special ...

Discover More

Finding Workbooks Containing Macros

Workbooks can contain macros, or not. It is entirely up to you whether they do or not, but at some future time you might want ...

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)

Enabling Filters by Default

Filtering can be a powerful way to work with large amounts of data in a worksheet. If you use filtering quite a bit, you may ...

Discover More

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

Discover More

Copying the Results of Filtering

Filtering is a great asset when you need to get a handle on a subset of your data. Excel even makes it easy to copy 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 for this tip:

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

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.

Links and Sharing