Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.