A week of dates and counting them using the COUNTIF function with contingencies

[j razz]

=COUNTIF(Contact_Date,">="&DATE(2007,3,18)-
COUNTIF(Contact_Date,"<="&DATE(2007,3,24)))

The above is the formula I am attempting to use.

Definitions:

Contact_Date is a list of dates that range over 2000 cells that could
span up to 1 year.

In the example above I am wanting to find any dates that range between
and include the dates: 3.18.2007 and 3.24.2007

Problem:

The formula I am using is wanting to look past the dates provided and
count any prior dates that do not fall within the specified range. I
do not know why or how to fix it.

Any ideas?

In case the formula is seen as html here it is spelled out:
=COUNTIF(Contact_Date,"Greater than="&DATE(2007,3,18)-
COUNTIF(Contact_Date,"Less than="&DATE(2007,3,24)))

 

[Pete_UK]

Try this:

=COUNTIF(Contact_Date,">="&DATE(2007,3,18)-
COUNTIF(Contact_Date,">"&DATE(2007,3,24)))

The first COUNTIf will count everything with a date later than 17th
March, and the second COUNTIF will count everything later than 25th
March. You were counting if earlier than 25th March.


Hope this helps.

Pete

 

[j razz]

Try this:

=COUNTIF(Contact_Date,">="&DATE(2007,3,18)-
COUNTIF(Contact_Date,">"&DATE(2007,3,24)))

The first COUNTIf will count everything with a date later than 17th
March, and the second COUNTIF will count everything later than 25th
March. You were counting if earlier than 25th March.

Hope this helps.

Pete





- Hide quoted text -

- Show quoted text -

Great! Thanks Pete. I appreciate the answer although I have moved to
a different formula that produces the same correct result:
=COUNT(IF((B1:B101>$E$1)*(B1:B101<$E$2),B1:B101)) once entered in the
formula bar you must press and hold ctrl, shift, and enter to get it
to take effect.

Now though I have another question, if I want to use the formula you
provided and add one more variable to it how would that be done?

For instance I would like to add to your formula:
=COUNTIF(In_Coming_Contact,">="&DATE(2007,3,18)-
COUNTIF(In_Coming_Contact,">"&DATE(2007,3,24))) a variable. The
variable being an item named AC in the dropdown list contained in the
In_Coming_Contact range. I suppose I would need to nest the
variable? I want it to count the amount of times AC shows up in the
cell with the drop down options in the given dates.

Thanks for the quick response!

j razz

 

[j razz]

Okay, I believe I have the formula for nesting the variable I want:
{=IF(In_Coming_Contact="AC",
(COUNT(IF((Contact_Date>=A4)*(Contact_Date<=B4),Contact_Date))))}
Again, if anybody uses this, you will need to remove the { } brackets
and when you enter the code in your formula bar press and hold ctrl
and shift and then press Enter.

I have tried it out and it appears to be working.

j razz

 

[T. Valko]

Are you sure you're getting the correct result with that formula?

See this screencap:

http://img410.imageshack.us/img410/6273/sampleph7.jpg

Try the formula you see in row 2. It's not an array entered formula so you
don't have to worry about remembering the squiggly brackets { }.

Biff

 

[j razz]

So much for that! It didn't work. It works okay if there are no
other variables in the drop down. For some reason I cannot get an
accurate response if I start changing the variables that populate the
cell from the drop down. For instance, I have 5 or so variables
contained in the drop down. AC is one, OIG is another. If I start
mixing them in cells going down the column, the results gathered by
the formula: {=IF(In_Coming_Contact="AC",
(COUNT(IF((Contact_Date>=A4)*(Contact_Date<=B4),Contact_Date))))} or
by this formula {=IF(In_Coming_Contact="OIG",
(COUNT(IF((Contact_Date>=A4)*(Contact_Date<=B4),Contact_Date))))}
cause unwanted results as it somehow does not utlize the portion of
the formula that says if in coming contact equals ac then... or if in
coming contact equals oig then...

Can someone walk me through this?

Thanks!

j razz

 

[j razz]

Thanks, that worked great. Would you mind telling me what I did wrong
so I can learn?
j razz

 

[j razz]

Thanks, that worked great. Would you mind telling me what I did wrong
so I can learn?
j razz

Okay, I have another question. So, let's say that I want to nest the
formula: =SUMPRODUCT(--(In_Coming_Contact="Other than client"),--
(Contact_Date>=A3),--(Contact_Date<=B3)) with a contingency. I want
to add a category on the front end that will allow me to know if these
results are under CK, TNC or under another option which will also be
pulled from a drop-down list called Application_Type. Thanks.

j razz

 

[j razz]

I answered my own question; I used this formula to produce the result
I wanted: =SUMPRODUCT(--(Application_Type="CK"),--(Contact_Date>=A3),--
(Contact_Date<=B3))

j razz