Countif, a value, and a date range

[backagain]

Here's the formula i have, and i'm obviously doing something wrong.

i have column with a name, then one with the date.

first thing i want to do is count up the number of names, but *only* in a specific date range(formula here is between today and next friday)

here's the formula that isn't working
=COUNTIF((Nov02!F1:F1000,"Brian*")*(Nov02!G3:G1000>"11/01/02)*(Nov02!G3:G1000<"11/08/02"))

i tried subsituting the sumproduct function and had no luck.

the other thing i'm trying to do(separate from above)

is try to automatically insert todays date(i don't want it to change to tomorrow, tomorrow when i open it) in column g when i type a name in column f.

 

[lenze]

I would suggest you have a look a the Database function DCOUNT, or (preferred) simply create a pivot table. Post some sample data and someone should be able to assist you with these methods

 

[Aladin Akyurek]

On 2002-11-01 15:05, backagain wrote:
Here's the formula i have, and i'm obviously doing something wrong.

i have column with a name, then one with the date.

first thing i want to do is count up the number of names, but *only* in a specific date range(formula here is between today and next friday)

here's the formula that isn't working
=COUNTIF((Nov02!F1:F1000,"Brian*")*(Nov02!G3:G1000>"11/01/02)*(Nov02!G3:G1000<"11/08/02"))

i tried subsituting the sumproduct function and had no luck.

the other thing i'm trying to do(separate from above)

is try to automatically insert todays date(i don't want it to change to tomorrow, tomorrow when i open it) in column g when i type a name in column f.

Let B1 and C1 house the criterion dates, with B1< C1 (that is, B1 is earlier in time than C1), date values like 11/01/02 and 11/08/02. Also, let A2 house the name condition, a value like Brian

=SUMPRODUCT((Nov02!$F$3:$F$1000=A2)*(Nov02!$G$3:G1000>B1)*(Nov02!$G$3:$G$1000<C1))

Note that F-range and G-range in sheet Nov02 must be of equal size.
This message was edited by Aladin Akyurek on 2002-11-01 17:09

 

[backagain]

I'm not getting it to work... Is there something with the last part of the code that isn't right? do the dates need to be in parenthesis?

I can't seem to get it to work

 

[backagain]

can anyone else help? i've been at this for hours

 

[Aladin Akyurek]

On 2002-11-01 17:03, backagain wrote:
can anyone else help? i've been at this for hours

How do you mean?

You probably didn't see the whole formula (due to a HTML quirk)... It's:

=SUMPRODUCT((Nov02!$F$3:$F$1000=A2)*(Nov02!$G$3:G1000>B1)*(Nov02!$G$3:$G$1000<C1))

See also

http://www.mrexcel.com/wwwboard/messages/8961.html

for more on SumProduct.

 

[IML]

On 2002-11-01 15:43, backagain wrote:
I'm not getting it to work... Is there something with the last part of the code that isn't right? do the dates need to be in parenthesis?

I can't seem to get it to work

If you are trying to put your dates in the formula instead of using the formula exactly as Aladin has suggested, you must coerce these into dates excel will recognize. The format for 11/01/02
would be
"11/01/02"+0.
Following Aladin's suggestion will greatly simplifly your life if these dates could ever change.

On a side note, are you looking for the column to equal Brian exactly, or just start with the string Brian? I just ask because of the Brian* in the first post.
This message was edited by IML on 2002-11-01 17:19

 

[backagain]

Great, that did the trick, but i still have 1 last problem

the "brian" name is just one of many.

adding the zero made it work--without that, it didn't. also, I added equal signs so that it was less than or equal to the dates.

one last thing though, on that other(2nd) request.

how can i get it to put the date in adjacent column g, when i type the name in column f.

i type "name"(in column f) and it puts todays date in the column(g), and the date stays the same?

 

[lenze]

2nd Question
Right click on the Sheet tab and choose View Code.

Enter this in the module
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 6 Then Exit Sub
Target.Offset(0, 1).Value = Now()
End Sub

 

[backagain]

Thank you!!!!!!!