I have a Google Spreadsheet of line items. One of the columns is a Date field. How can I query (or use a formula) to, for example, get the count of records where the Date field is within February of 2011?
Note: Could use a better title
Google Sheets Formulas – How to Query for Count of Dates
formulasgoogle sheets
Related Topic
- Google Sheets – How to Fix QUERY Error for BY Column
- Google Sheets – Count Days Present in Overlapping Ranges
- Google Sheets – Using QUERY with IMPORTRANGE and Dates
- Google Sheets – Smarter Way to Query with Multiple Criteria
- Google Sheets – Count Query Based on Multiple Conditions
- Google-sheets – IMPORTRANGE QUERY formula filtered by dates range and values from cell
- Google Sheets – Convert ISO 8601 Strings to Dates
- Google Sheets – How to Query Dataset While Calculating Cumulative Values
Best Answer
You need to use 2 formulas.
Create a month column and use the following formula to "display" the month and year:
=text(B1,"mmmm YYYY")I assumed that the data was in column B, you will need to adjust accordingly. Copy this formula down the entire column
Create a column to hold the labels for the calculation.
Put the value for each month in the cells (January 2011, February 2011...)
Create another column to hold the results, you will need to put the following formula into the cell next to the January 2011 label:
=countif(C:C,D1)I assumed that the labels are in column D and the results of the text formula are in column C, you will have to adjust accordingly. Copy this formula into the rest of the cells next to the labels.
countif()is a very useful formula. The key to this was the use of the text command to generate a result just showing month and year.