Counting Number of Different Instances

K

Krishnakumar

Well-known Member

Joined

Feb 28, 2003

Messages

2,615

• Dec 5, 2006

• #2

If you have Morefunc Add-In then

=COUNTDIFF(IF(DeptRange="DeptName",ScheduleRange))

Confirmed with Ctrl+Shift+Enter

HTH

 

Upvote 0

Yevette

Active Member

Joined

Mar 8, 2003

Messages

336

• Dec 5, 2006

• #3

Unfortunately I do not have the option of adding MOREFUNC on to my work computer. Is there another option?

 

Upvote 0

J

jindon

MrExcel MVP

Joined

Aug 21, 2004

Messages

16,995

• Dec 5, 2006

• #4

How about vba?

 

Upvote 0

Yevette

Active Member

Joined

Mar 8, 2003

Messages

336

• Dec 5, 2006

• #5

The spreadsheet is fairly small and for my purposes I don't think it warrants using VBA. But thanks anyway.

Yevette

 

Upvote 0

K

Krishnakumar

Well-known Member

Joined

Feb 28, 2003

Messages

2,615

• Dec 5, 2006

• #6

=SUMPRODUCT(($A$2:$A$20=D2)*(ROW($A$2:$A$20)-ROW($A$2)+1=MATCH($A$2:$A$20&$B$2:$B$20,$A$2:$A$20&$B$2:$B$20,0)))

where;

A2:A20=DeptRange
B2:B20=ScheduleRange
D2=DeptName

HTH

 

Upvote 0

Yevette

Active Member

Joined

Mar 8, 2003

Messages

336

• Dec 5, 2006

• #7

Kris, Worked like a charm thank you so much!

Perhaps you can help me with one more thing. Can you help me with a VBA code that will save/and toggle a file to read-only after, say 5 minutes? We have a shared spreadsheet that requires various users to access it and enter data when they receive it. No user should be in the file for more than 5 minutes. Some users go away and leave the file open so other users can't access it. What do you think? Is this doable? Thanks for your help!

Yevette

 

Upvote 0

K

Krishnakumar

Well-known Member

Joined

Feb 28, 2003

Messages

2,615

• Dec 5, 2006

• #8

Hi,

Checkout the following links.

1. Auto Close

2. Auto Close

HTH

 

Upvote 0

Yevette

Active Member

Joined

Mar 8, 2003

Messages

336

• Dec 11, 2006

• #9

The autoclose code that you provided works great. However, I'm running across a small problem. Can you help me with a code that I could add that would "NOT" close the file if the file is in "Read-Only". Thanks for your help.

Yevette

 

Upvote 0

K

Krishnakumar

Well-known Member

Joined

Feb 28, 2003

Messages

2,615

• Dec 11, 2006

• #10

Hi,

Try this

 

Upvote 0