0

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.

  • So what’s your question? – Alex May 21 '14 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. – user69140 May 21 '14 at 17:45
  • 1
    Best is to share a doc and to show us the expected outcome. – Jacob Jan Tuinstra May 22 '14 at 18:43
1

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 $fF2)

(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. – Jacob Jan Tuinstra May 22 '14 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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