Forum Discussion
Counting words in one column based on dates in another column.
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
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.
Hi Segey and Matt
why not use countifs?
YossiHi 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.