Counting words in one column based on dates in another column.

Copper Contributor

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?

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

Matt,

 

First two links I found with PivotTable basics

https://support.office.com/en-us/article/Create-a-PivotTable-to-analyze-worksheet-data-A9A84538-BFE9...

 

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

PivotTable.JPG

 

After that is adjusting - how to group dates, how to design the table.

Hi Segey and Matt
why not use countifs?

Yossi

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.

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. 

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

Counting.JPG

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

CountingTable.JPG

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

 

 

 

 

@Sergei Baklan 

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!