Forum Discussion
Counting words in one column based on dates in another column.
Hi Everyone,
I am looking for help coming up with a formula to do the following...
In the A column I have a clients status which is either "Completed", "AMA", or "Admin'
In the B column I have a discharge date which ranges from Jan 2015 to November 2017
What I'm looking to do is count the occurrences of of the clients status by month over the past 3 years. For example I want to see the count of how many clients were Completed, AMA, or Admin between January 1st 2015 and January 31st 2015 and then so on month by month until October 2017 broken down by month.
I've tried to look around to find a formula that would consider the date in Column B and then look at the status word in column A to count but have had not luck.
Any help with this is much appreciated.
Thank You
9 Replies
Hi Matt,
Did you try PivotTable?
I stumbled upon this discussion when trying to solve a similar issue and pivot tables completely changed everything for me. I had a little bit of time to understand the basics and I can't believe I hadn't been using them all along. Thank you for your input on this question!
kellymuzy , glad it helped
Hi Sergei,
No I have not. I am rather new to excel and am not familiar with Pivot Tables or how they work. Can you provide any instructions on how to use one for the particular problem.
Thanks,
MattMatt,
First two links I found with PivotTable basics
http://www.excel-easy.com/data-analysis/pivot-tables.html,
google will give much more.
For your case
Better if you work with Excel Tables, if not the range works as well. Select one which is with your data,
in ribbon Insert->Pivot Table.
PivotTable pane will appear, add Date to rows, Status to Columns and Values. Result looks like
After that is adjusting - how to group dates, how to design the table.
