Hello. I have a list of calculations that I have to perform each week and I
decided to use excel to simplify the task. I will use generic examples. I
have a group of agents which call on any of 4 different programs. We will
say program A-D. Each program has a different goal. Each agent has an
average sales per hour for the week. Instead of pasting in a specific
formula for each different program, I want to be able to have excel
determine which formula to use based on what program.

               A                   B                   C                 D

1       John Doe           ProgA             SPH            %ofGoal
2       Jane Doe           ProgB              SPH            %ofGoal
3       Dick Doe           ProgC             SPH            %ofGoal
4       Harry Doe         ProgD             SPH             %ofGoal

I want to be able to do the following.

If the text in column B = ProgA then divide the value in column C by GoalA
to find %ofGoal
If the text in column B = ProgB then divide the value in column C by GoalB
to find %ofGoal
etc.

I have come up with something like

=IF(B2:B56=ProgA,"=SUM(C$2:C$56/GoalA)")

but it doesn't work and it only checks for one possibility

Is what I want to do even possible? I have my spreadsheet set up so far so
that I can copy paste the information from our intranet and I have named
cells which you just enter the value for goal each week since it changes.
(GoalA, GoalB, etc.)