Excel formula question

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

Question

Excel Faculty sheet

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?

Excel jpg image


ANSWER: To sum Hours, use

=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

Answer

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

Excel Sheet

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?

Answer

Jeff

I am a bit confused with the question, but you should be able to use the SUMIF() formula to total the hours that each faculty member is scheduled. The formula keys off of criteria that needs to be on the left of the range you are summing. So if the criteria is a name of a faculty member then the name needs to be on the left of the hours scheduled. For example if the hours in your example were in column J, then the formula would look like this. +SUMIF(H$1:J$18,M1,J$1:J$18) In this example the M1 is the criteria and is referencing the cell that contains "Joe Smith" so the formula will sum all of the hours for Joe.

This formula would be in cell O1 and can be copied down to the remainding rows. This formula works like the COUNTIF formula but it sums the numbers instead of counting the instances of occurance.

To put the hours in column J you can use a simple +F1 formula in cell J1 and then copy it down to the remainding rows.

Advertisement

©2020 eLuminary LLC. All rights reserved.