Totals by Month using a date column

Olga N Barabash

Is there a formula to count the # of entries for each month, using a system created date column? It seems like this doesn't exist unless you create a new column that says either 12 or December by looking at the date column..

 

thanks!

J. Craig Williams

Olga,

 

Either you need to have a column to get the month's number and then do a COUNTIF or the formula is very complicated and long because you need to do determination in one formula.

 

If, however, you wanted 12 results (that is showing Jan's total and Feb's total and...) in a section at the top or bottom of your sheet, that's pretty simple, sort of.

 

Be aware, it is a LOT easier to put numbers into Text/Number columns than it is into put them into Date columns, even though it seems to "look better" in the same column.

 

For example:

 

This:

 

=COUNTIF(Finish31:Finish33, MONTH(@cell) = 1) + ""

 

will return the number of dates that fall in January where the formula is in a Date column.

The (+ "") is added to force it to a text (which has the added disadvantage that you can't add it without putting it to a number using the VALUE function.

 

It also throughs an error if the dates are blank.

And has to be modified if your data set grows.

 

If you want only Jan-2017 and nont Jan-2018, then you can make it more complicated too.

 

A trick to get the count back into the date column is to put your calculation somewhere else and hide that column.

In the date column, just get the value

=[MyHiddenColumn]1 + ""

 

You still need to turn it into text but the number is now available without worrying about using VALUE()

 

Hoe this sparks some ideas.

 

Craig