Google-sheets – Conditional counts based on dates

google sheetsgoogle-sheets-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.

Best Answer

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.)

Related Topic