# Calendar/Scheduling Sheet Summary

1. ## Calendar/Scheduling Sheet Summary

I manage a small business and have been asked to summarize a calendar we have in Excel. Basically we clean buildings every quarter - 1x a year is a full cleaning, 2x are just outside, and 1x sweep/vacuum but not full cleaning --> full and sweep can't be right after each other. We schedule out about 2 years but things change regularly. Each person has like 20-25 buildings. Attached is a short example of two years.

F = full, O = outside, S = sweep

I'm not an Excel pro by any means but took some courses in college years ago and remember a little. Is there a way to summarize these by month and employee?

I have a new sheet where I made a drop down for Employee and Month but I can't seem to find a way to summarize them nicely.

I've tried a bunch of things but without success - pivot tables, combinations of giant if statements using index/match functions, arrays

Basically if Cleaner = A and Month = January and any cell within those parameters = F, then list the names of those locations.

Can anyone help here? Or is it too difficult for a more novice user like me to do in Excel? Any help would be appreciated - thanks.

2. ## Re: Calendar/Scheduling Sheet Summary

in H6

=IFERROR(INDEX(Schedule!\$A\$2:\$A\$100,SMALL(IF((Schedule!\$B\$2:\$B\$100=Summary!\$B\$2)*(INDEX(Schedule!\$C\$2:\$Z\$100,,MATCH(Summary!\$B\$1,Schedule!\$C\$1:\$Z\$1,0))="F"),ROW(Schedule!\$A\$2:\$A\$100)-ROW(\$A\$2)+1,""),ROWS(\$A\$2:\$A2))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then copy down

copy to other columns and change F to O and S respectively

3. ## Re: Calendar/Scheduling Sheet Summary

Thank you John! I spent like 2 hours and got nowhere lol.

4. ## Re: Calendar/Scheduling Sheet Summary

See the attached ..

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1