Counting Number of Different Instances

Yevette

Active Member
Joined
Mar 8, 2003
Messages
336
Hello Again,

I would like to count the number of instances of different schedules shows in one department. For example for dept. 1, I would like to know how many different schedules are in that dept. I have 48 schedules and 60 departments (numbered 1-60). I would like to know how many "different schedules" dept 1 has and so on. I don't think the frequency function will work. Is there a way I can use sumproduct to get the result I'm looking for? The information is on a separate worksheet in colums (Col. A holds the dept and Col. B holds the relative schedule). Clear as mud? Thanks for your help!

Yevette :eek:
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Unfortunately I do not have the option of adding MOREFUNC on to my work computer. Is there another option?
 
Upvote 0
The spreadsheet is fairly small and for my purposes I don't think it warrants using VBA. But thanks anyway.

Yevette
 
Upvote 0
=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
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
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

Forum statistics

Threads
1,212,927
Messages
6,110,730
Members
448,294
Latest member
jmjmjmjmjmjm

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
Back
Top