PLSSS Help, SUMIFS and IF

[sarahbunny1]

I have an exercise which is due today, I need help. Please.... Whoever helps me, I will flash to you....desperate lol

[TABLE="width: 909"]
<colgroup><col><col span="2"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Contract ID[/TD]
[TD]Student ID[/TD]
[TD]Teacher ID[/TD]
[TD]Contract Start Date[/TD]
[TD]Contract End Date[/TD]
[TD]Lesson Type[/TD]
[TD]Lesson Length[/TD]
[TD]Lesson Monthly Cost[/TD]
[TD]Monthly Rental Cost[/TD]
[/TR]
[TR]
[TD]3100[/TD]
[TD]CAR7534[/TD]
[TD]13-1100[/TD]
[TD]2009-03-31[/TD]
[TD]2010-03-31[/TD]
[TD]Piano[/TD]
[TD]30[/TD]
[TD]130.00[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]3102[/TD]
[TD]MEH7551[/TD]
[TD]63-1554[/TD]
[TD]2010-05-18[/TD]
[TD]2011-05-18[/TD]
[TD]Guitar[/TD]
[TD]30[/TD]
[TD]100.00[/TD]
[TD]30.00[/TD]
[/TR]
[TR]
[TD]3103[/TD]
[TD]MAK7556[/TD]
[TD]34-4506[/TD]
[TD]2010-06-15[/TD]
[TD]2010-12-15[/TD]
[TD]Violin[/TD]
[TD]30[/TD]
[TD]100.00[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]3105[/TD]
[TD]BUR7559[/TD]
[TD]22-0102[/TD]
[TD]2009-04-08[/TD]
[TD]2010-04-08[/TD]
[TD]Cello[/TD]
[TD]60[/TD]
[TD]280.00[/TD]
[TD]50.00[/TD]
[/TR]
[TR]
[TD]3107[/TD]
[TD]MIL7512[/TD]
[TD]22-0102[/TD]
[TD]2010-06-15[/TD]
[TD]2011-06-15[/TD]
[TD]Piano[/TD]
[TD]60[/TD]
[TD]260.00[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]3108[/TD]
[TD]SHA7522[/TD]
[TD]63-1554[/TD]
[TD]2009-06-17[/TD]
[TD]2010-06-17[/TD]
[TD]Guitar[/TD]
[TD]30[/TD]
[TD]100.00[/TD]
[TD]30.00[/TD]
[/TR]
[TR]
[TD]3110[/TD]
[TD]HIR7521[/TD]
[TD]17-1798[/TD]
[TD]2009-09-09[/TD]
[TD]2010-09-09[/TD]
[TD]Percussion[/TD]
[TD]30[/TD]
[TD]120.00[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]3113[/TD]
[TD]YAM7535[/TD]
[TD]91-0178[/TD]
[TD]2009-09-10[/TD]
[TD]2010-09-10[/TD]
[TD]Violin[/TD]
[TD]60[/TD]
[TD]280.00[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]3114[/TD]
[TD]MEN7541[/TD]
[TD]70-4490[/TD]
[TD]2009-09-10[/TD]
[TD]2010-09-10[/TD]
[TD]Guitar[/TD]
[TD]60[/TD]
[TD]200.00[/TD]
[TD]30.00[/TD]
[/TR]
</tbody>[/TABLE]

1. Based on the cost of a month for each contract and including the monthly rental, create a flexible way of isolating the total income for 2010 for each of the following “Lesson Types”. Piano, Percussion and Violin. Take a copy of the worksheet for each one and paste that copy onto a new worksheet, renaming the worksheet tab to reflect the content. Three new worksheets in all.


2. Find out which Instructor gathered the most revenue for us and take a copy to place on a new worksheet with named tab.

3. Create a pivot table on its own worksheet, with the Instructor Names as the Row labels and the Lesson Type as the column labels. Set up a filter on Student Name. With no filter applied, create a Pie Chart for the Piano Lesson Type and place it on the sheet with the Pivot table underneath the table. Make sure there are sufficient labels and legend to make the resulting chart very clear.

4. Use the filter on the pivot table to determine which student spends the most money on lessons with us in the year 2010. When you find that student copy the pivot table and place on its own named sheet.
 
5. There is a quicker way to find the student that spends most money on lessons, rather than first having to create the Pivot Table then go through the filters. See if you can figure it out and then create a new named sheet, place a complete copy of all the data onto that sheet and then write up the instructions about how to determine the student spending the most on lessons.

 

[Blade Hunter]

1. You can do this, it's just copying the sheet and renaming it 3 times

2 On one of the sheets use the sumifs formula against a unique list of teachers to add the lesson cost and the rental cost (i think). You can get a distinct list by copying and pasting somewhere else and choosing remove duplicates from the data menu

3 on another of the sheets add a pivottable from the insert menu and drag the data points according to the instructions, this is REALLY easy

4 Drag the student object to the filter section in the pivot builder, if that is missing click in the pivot table to get it back. change the filter through each student to find the largest (This is a really stupid way to do this but they specifically said to do it this way)

5 Use a sumifs formula against the student. Similar to the second one copy the column, paste somewhere else, remove dupes, apply a sumifs formula.

Post back if you get stuck we are all happy to "help" but not do your homework for you.

I have given you the tools to quickly and easily solve this for yourself and you will learn the answer this way, if I just gave it to you you wouldn't learn. I estimate with my instructions this should take no more than 15 minutes for someone with very little Excel experience.

No need to flash thanks The sense of accomplishment you will personally feel when you get this right is reward enough.

 

[sarahbunny1]

ohhhh my Mr. Chamring, you are soooo sweet and helpful......
I decide to do more for you. just let me know what kind of photos you like to take. I will do it.