# Conditional counts based on dates

I am creating a master tracker for all the technology in my school and it is pulling data from many sheets and placing it in to blocks and I am having trouble figuring out the following

School|Room|Model|Bulb Replaced|Bulb Replaced Date|Date Filter Cleaned|Bulb Model

each of those represents a column. Now what I want is a block to the side to count blocks that have data in them in the "Date Filter Cleaned" column that are within 6 months.

Then for the tricky part: I need a way to create something that will count between 2 dates, this way I can change them per school year, e.g., count between Sept 1 2013- June 1 2014. I could then just recreate and edit for each school year.

I have never done any sort of conditional counting based on dates so I do not know how.

– Alex
May 21, 2014 at 16:02
• how to do as described. basically i am looking for someone who knows how to do the formatting codes, or a script for the above mentioned. May 21, 2014 at 17:45
• Best is to share a doc and to show us the expected outcome. May 22, 2014 at 18:43

The functions you need to use are `SUMIF` and `COUNTIF`.

For example, to count all the bulbs with DateFilterCleaned within six months, and assuming that DateFilterCleaned is within six months, you need to:

Make a helper-column which calculates the number of days eg `E3 = now()-D3`

And then a formula like this `=COUNTIF(E:E, ">183")`

To make it general purpose for each school year, just put the start-date and end-date into separate cells (eg `F1` and `F2`) and refer to them using absolute addressing (eg `\$f\$1` and `\$f2`)

(This isn't the full solution, but is hopefully enough hints to get you started. Note that there is absolutely no need to use scrips for any of this.)

• Somehow I feel that you dislike scripts. -1 for an incomplete solution. May 22, 2014 at 18:45