Nov 02 2017
03:22 PM
- last edited on
Jul 25 2018
10:20 AM
by
TechCommunityAP
Nov 02 2017
03:22 PM
- last edited on
Jul 25 2018
10:20 AM
by
TechCommunityAP
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
Nov 02 2017 03:25 PM
Hi Matt,
Did you try PivotTable?
Nov 02 2017 03:26 PM
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,
Matt
Nov 02 2017 03:39 PM
Matt,
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.
Nov 02 2017 11:13 PM
Nov 03 2017 03:37 AM
Hi Yossi,
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.
=COUNTIFS($A$2:$A$480,">=1/1/2016",$A$2:$A$480,"<=3/31/2016",$B$2:$B$480,"*Admin*")
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
Hi Matt,
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
=EDATE(<date>,3)
not to enter all dates manually)
and for such data structure
the formula in F3 will be
=COUNTIFS($A$2:$A$600,">="&$D3,$A$2:$A$600,"<="&$E3,$B$2:$B$600,F$2)
you may frag it down and when entire column to the right.
The alternative (in L3) is
=SUMPRODUCT(($A$2:$A$600>=$J3)*($A$2:$A$600<=$K3)*(ISNUMBER(SEARCH(L$2,$B$2:$B$600))))
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
=COUNTIFS(Table1[Date],">="&$D3,Table1[Date],"<="&$E3,Table1[Status],F$2)
and
=SUMPRODUCT((Table1[Date]>=$J3)*(Table1[Date]<=$K3)*(ISNUMBER(SEARCH(L$2,Table1[Status]))))
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!
Mar 29 2019 08:19 AM
@kellymuzy , glad it helped