How to make counts in between dates?

**Daniel86** · 04-04-2013, 05:41 PM

Hi guys,

So, I've got this thing that I just can't make to work. Basically, I have a bunch of data a windmill park which is under establishment.
The thing is that the windturbines undergo to stages called "commissioning" (when its being built and tested) and "commercial operation" (when its finished and ready to use).

I need to get a daily ratio between the number of mills in commissioning and commercial operation. But to do that, I need to get the amount of mills being in either category each day.

I figured out that I maybe could solve the challenge by using the COUNTIF formula, but it doesnt seem to catch all the criteria since a mill is in commiosing in between two days (between the day it began its commissioning and the day it ended it/or still going).

Here's an idea of how the data looks like. Its pretty straightforward information wise.

Any ideas?

idea.JPG

**FDibbins** · 04-04-2013, 05:48 PM

would something likethis work for you?

=COUNTIF(C:C,"<="&C3)

You can copy that over as-is to get Commertial

**Richard Buttrey** · 04-04-2013, 06:02 PM

Hi

Does the attached help?

**Daniel86** · 04-08-2013, 08:23 PM

Hi Richard,

I came to the same conclusion as your suggestion, however it still doesn't do the job.
=COUNTIF(C:C,"<="&C3) only counts windmills that are in "commissioning OR commercial operation" or "commercial operation". It doesnt distinguish if a wind mill is in commercial or commissioning before the given date the criteria is set to - hence the logic dictates that it counts windmills in commissioning and commercial operation as "in commissioning".... if you know what I mean? It's close, but no cigar.

It took me some time to see it, but look at it and you will see.

Note: I found it helpful to reduce the data to only contain around 10 windmills. Then you quickly see the problem that the formula doesnt fully know how to distinguish between "in commissioning" and "in commercial operation" as to absolute different values that are mutually exclusive.

This task is a pain in the b*tt!

**Richard Buttrey** · 04-09-2013, 11:18 AM

Hi,

I'm not sure I understand the question then.
Can you offer some specific examples of the results that you expect for a sample of dates and importantly a how you have arrived at the result with reference to specific cells.

**Daniel86** · 04-09-2013, 11:33 AM

Hi Richard,

I know. It took me some time to see it too.
I reckon the simplest way to put it would be that the formula looks at one specific date and then counts the amount of mills that falls within this criteria. That's where the mistake happens. If we take the date, say 1st of march 2013 - the current formula will include mills where the date for commissioning and commercial operation BOTH are lower than 1st of March 2013.. In that way, the formula counts mills that are "in commissioning OR commercial operation" but doesn't distinguish between the two