SOLVED
Home

Creating a Complex Points-Calendar System

%3CLINGO-SUB%20id%3D%22lingo-sub-572410%22%20slang%3D%22en-US%22%3ECreating%20a%20Complex%20Points-Calendar%20System%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-572410%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3EI%20want%20to%20create%20a%20point-calendar%20system%20for%20a%20schedule%20that%20I'm%20creating%20for%20my%20team.%20I'm%20new%20to%20excel%2C%20and%20my%20many%20google%20searches%20led%20me%20to%20be%20more%20confused.%20Here's%20what%20I'm%20trying%20to%20create%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F113678i76D16A844ED049FA%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Excel%20Trial.jpg%22%20title%3D%22Excel%20Trial.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20bottom%20table%20is%20a%20calendar%2C%20and%20each%20day%20is%20assigned%20a%20number%20of%20points.%20Each%20person%20can%20pick%20whichever%20shift%20they%20want%20to%20take%20on%20any%20day.%20In%20the%20image%2C%20Tony%20decided%20to%20take%20a%20Monday%20shift%20(Week%201)%2C%20a%20Wednesday%20shift%20(Week%202)%2C%20and%20a%20Saturday%20shift%20(Week%203).%20This%20gives%20Tony%20a%20total%20of%20%3CSTRONG%3E4%20points%3C%2FSTRONG%3E%20for%20the%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20create%20this%2C%20through%20a%20series%20of%20formulas%2C%20where%20I%20can%20simply%20put%20in%20a%20name%20into%20the%20calendar%20and%20it%20will%20automatically%20calculate%20the%20total%20number%20of%20points%20in%20another%20table%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20tips%2C%20help%2C%20and%20suggestions%20are%20appreciated.%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-572410%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-572453%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Complex%20Points-Calendar%20System%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-572453%22%20slang%3D%22en-US%22%3EPlease%20attach%20your%20sample%20file%20so%20that%20I%20won't%20have%20to%20recreate%20your%20data%20layout%20when%20I%20test%20the%20formula.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-572682%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Complex%20Points-Calendar%20System%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-572682%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342898%22%20target%3D%22_blank%22%3E%40Abe_Ryker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20modified%20the%20layout%20of%20your%20data.%20In%20the%20attached%20file%2C%20the%20formula%20in%20B4%2C%20copied%20down%20rows%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUMPRODUCT((B%2413%3AH%2417%3DA4)*B%2411%3AH%2411)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-574830%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Complex%20Points-Calendar%20System%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-574830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWow!%20This%20is%20exactly%20what%20I%20was%20looking%20for.%20Thank%20you%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-575357%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Complex%20Points-Calendar%20System%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-575357%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20very%20much%20welcome!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-582921%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Complex%20Points-Calendar%20System%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-582921%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342898%22%20target%3D%22_blank%22%3E%40Abe_Ryker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20sketched%20out%20a%20potential%20solution%20yesterday%20morning%20but%20the%20got%20side%20tracked.%3C%2FP%3E%3CP%3EThe%20worksheet%20formula%20reads%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20SUM(assigned)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ewhere%20the%20points%20assigned%20is%2C%20in%20turn%2C%20given%20by%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20(Calendar%3DName)%20*%20TRANSPOSE(Points)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThe%20reason%20for%20the%20named%20formula%20(visible%20in%20Name%20Manager)%20is%20to%20overcome%20Excel's%20tendency%20to%20break%20up%20array%20calculation%20with%20implicit%20intersection.%26nbsp%3B%20Other%20ways%20of%20preventing%20the%20implicit%20intersection%20step%20are%201.%20To%20use%20Ctrl%2BShift%2BEnter%2C%202.%20To%20use%20a%20limited%20number%20of%20functions%2C%20including%20SUMPRODUCT%2C%20that%20process%20arrays%20correctly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-582946%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Complex%20Points-Calendar%20System%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-582946%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETransposing%20the%20points%20table%20simplifies%20both%20the%20formula%20and%20the%20UI%20design!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Abe_Ryker
New Contributor

Hi!

I want to create a point-calendar system for a schedule that I'm creating for my team. I'm new to excel, and my many google searches led me to be more confused. Here's what I'm trying to create:

Excel Trial.jpg

The bottom table is a calendar, and each day is assigned a number of points. Each person can pick whichever shift they want to take on any day. In the image, Tony decided to take a Monday shift (Week 1), a Wednesday shift (Week 2), and a Saturday shift (Week 3). This gives Tony a total of 4 points for the month.

 

Is there a way to create this, through a series of formulas, where I can simply put in a name into the calendar and it will automatically calculate the total number of points in another table?

 

Any tips, help, and suggestions are appreciated. Thank you!

6 Replies
Highlighted
Please attach your sample file so that I won't have to recreate your data layout when I test the formula.
Highlighted
Solution

@Abe_Ryker 

I have modified the layout of your data. In the attached file, the formula in B4, copied down rows, is: 

=SUMPRODUCT((B$13:H$17=A4)*B$11:H$11)

Highlighted

@Twifoo 

Wow! This is exactly what I was looking for. Thank you so much!

Highlighted
You’re very much welcome!
Highlighted

@Abe_Ryker 

I sketched out a potential solution yesterday morning but the got side tracked.

The worksheet formula reads

= SUM(assigned)

where the points assigned is, in turn, given by

= (Calendar=Name) * TRANSPOSE(Points)

The reason for the named formula (visible in Name Manager) is to overcome Excel's tendency to break up array calculation with implicit intersection.  Other ways of preventing the implicit intersection step are 1. To use Ctrl+Shift+Enter, 2. To use a limited number of functions, including SUMPRODUCT, that process arrays correctly.

 

Highlighted

@Twifoo 

Transposing the points table simplifies both the formula and the UI design!

Related Conversations
Booking Errors
fredzahn in Microsoft Bookings on
3 Replies
Reserving a meeting room via Bookings
HannuT in Microsoft Bookings on
0 Replies
O365 Group Calendar
Jeff Harlow in Office 365 Groups on
7 Replies