Countif, a value, and a date range

backagain

New Member
Joined
Oct 31, 2002
Messages
6
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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Oct 31, 2002
Messages
6
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

New Member
Joined
Oct 31, 2002
Messages
6

ADVERTISEMENT

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

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743

ADVERTISEMENT

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

New Member
Joined
Oct 31, 2002
Messages
6
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

Forum statistics

Threads
1,144,120
Messages
5,722,596
Members
422,447
Latest member
knopp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top