Nov 02 2017
- last edited on
Jul 25 2018
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.
Nov 02 2017 03:26 PM
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.
Nov 02 2017 03:39 PM
First two links I found with PivotTable basics
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.
Nov 03 2017 03:37 AM
Nov 03 2017 03:37 AM
For such kind of tasks formulas (COUNTIFS, SUMPRODUCT, whatever) is less flexible and more time consuming solution compare to PivotTables.
With formulas you have to build manually resulting table, add formulas to it, test them to be sure they work correctly. And repeat the same if one day you add/change the status, decide to calculate averages not only counts, etc.
PivotTable, especially in combination with Table, gives the same in couple of clicks. With adding new data you shall expand resulting table with formulas manually, with PivotTable just refresh it.
Nov 03 2017 03:24 PM
Sergei and Yossi,
Thanks for both of your inputs on this. I started messing around with pivot tables and I do see that they will be easier to use in the future once I get more familiar with them. In the meantime I was able to use the following formula to achieve my goal.
Then I would simply change dates, or the word to get what I was looking for for that particular part of the table I was building.
Thanks again to you both.
Nov 05 2017 02:10 AM
When perhaps easier and more reliable will be if you define start and end dates of you periods in the sheet cells (you may use
not to enter all dates manually)
and for such data structure
the formula in F3 will be
you may frag it down and when entire column to the right.
The alternative (in L3) is
With this it's not necessary to correct the formula for each period/status. However, if you range with data will be out of one defined in formula you have to correct the formulas again.
From that point of view (and not only) better to convert you range into the table -stay on any cell within it and press Ctrl+T.
It looks like
and above formulas could be transferred to
sure instead of adding "*" in columns names you may use
, "*" & F$2 & "*"
in first formulas.
Sample is attached
Mar 29 2019 07:56 AM
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!