Count number of occurrences by month
excel, formula
Solution
Use a pivot table. You can manually refresh a pivot table's data source by right-clicking on it and clicking refresh. Otherwise you can set up a worksheet_change macro - or just a refresh button. Pivot Table tutorial is here: http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/
1) Create a Month column from your Date column (e.g. `=TEXT(B2,"MMM")` )
2) Create a Year column from your Date column (e.g. `=TEXT(B2,"YYYY")` )
3) Add a Count column, with "1" for each value
4) Create a Pivot table with the fields, Count, Month and Year 5) Drag the Year and Month fields into Row Labels. Ensure that Year is above month so your Pivot table first groups by year, then by month 6) Drag the Count field into Values to create a Count of Count
There are better tutorials I'm sure just google/bing "pivot table tutorial".
Problem
I am creating a spreadsheet with all my data on one sheet and metrics on the other. On sheet 1 in cells `A2:A50` I have the dates in this format (4/5/13). On sheet 2 in cell `E5` I have April and I want it to total the number of PO's created in F5. How can I do this? I have tried using `=COUNTIF('2013'!$A$2:$A$50,'2013 Metrics'!E5)`. I have a feeling that since my range is in 4/5/13 format and my criteria is April that won't work. I was able to use this formula for total spend by month: `=SUM(IF(MONTH('2013'!$A$2:$A$19)=4,'2013'!$D$2:$D$19,0))` but not luck with how many PO's by month.