# Excel formula question

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:31 PM GMT

## Question

QUESTION: I am trying to figure out a formula in Excel. I have a spreadsheet which lists various professors and the courses I want to schedule them for.

In column N, I have used a formula from a colleague which calculates the number of Sections/Courses that I have penciled them in for.

Ideally I would like to put in a formula which now calculates the Hours that all these courses add up to. For example, Joe Smith is penciled in for 3 Courses/Sections totaling 11 hours (the hours for each course are in column F).

The ultimate goal here is to populate the table on the left with the instructors as the table on the right calculates the number of sections as well as the hours that all of the sections add up to. The formula for column O is what I need. Any chance you could help out?

=SUMIF(\$H\$3:\$H\$19,M3,\$F\$3:\$F\$19)

---------- FOLLOW-UP ----------

QUESTION: The above formula worked beautifully. Thank you! Two follow up questions:

1)the sheet has 368 rows - is there a quicker/easier way to highlight multiple columns and rows for re-sorting purposes other than the typical way to do this? Specifically i have been highlighting columns A-I and rows 3-368 and using the sort tool. This has become tedious.

2) I have three tabs at the bottom of the sheet (Course/Program/Faculty). The latter two tabs are currently empty. Ideally I would like to have all three tabs linked. Meaning I would have the same information on all three tabs and clicking on the bottom tabs would give me three different views of the information. Ideally this would also be setup that if I made changes on any of the three tabs it would update the other two.

Not sure how possible any of this is and not confident enough to try it on my own.

Jeff

1) Select range A3:I368. From the Formulas ribbon, click Define Name, and in the New Name dialog box, enter under Name:

rngToSort

and click OK.

Now select and cell on your sheet. Click the drop-down arrow for the name box. (The name box is near the upper left, just above cell A1 of your sheet.) From the name box, choose rngToSort, and your range will get selected.

2) Example: on Sheet2, cell A1, enter

=Sheet1!A1

Then, whatever text or value you have in cell A1 of Sheet1 will appear on Sheet2. To have it go both ways... so that an entry on Sheet1 appears on Sheet2, and vice-versa is much much more complicated. You'd need to learn Visual Basic for Applications ("vba" or "macros") to accomplish this.

## Question

I am trying to figure out a formula in Excel. I have a spreadsheet which lists various professors and the courses I want to schedule them for.

In column N, I have used a formula from a colleague which calculates the number of Sections/Courses that I have penciled them in for.

Ideally I would like to put in a formula which now calculates the Hours that all these courses add up to. For example, Joe Smith is penciled in for 3 Courses/Sections totaling 11 hours (the hours for each course are in column F).

The ultimate goal here is to populate the table on the left with the instructors as the table on the right calculates the number of sections as well as the hours that all of the sections add up to. The formula for column O is what I need. Any chance you could help out?