Forum Discussion

Abe_Ryker's avatar
Abe_Ryker
Copper Contributor
May 16, 2019
Solved

Creating a Complex Points-Calendar System

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
Formulas and Functions
  • Twifoo's avatar
    Twifoo
    May 16, 2019

    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)

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 17, 2019

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.

 

Resources