Excel SUMIF Function

Question

In cell C2 of the Summary Worksheet, I am needing to use the SUMIF function to calculate the total Tuition costs for the students enrolled in the English Program. Do the same for C3 and C4. Then I need to use the SUMIF function to calculate the required totals for cells D2 through F4. Thank you

Answer 1

Hi, 

Thanks for your reply, but I have figured out how to apply the proper formulas to my data. Thank you for your answer though!

Answer 2

The summary worksheet should be able to be handled by a PivotTable.

It would help if you uploaded that sample sheet to OneDrive and gave us a Share Edit link to it (assuming that is not real student information

I've recreated your date, but your data sheet does not match the summary info, ie no french data

https://1drv.ms/x/s!Am8lVyUzjKfphnOt0Aupa0IVPuHv

It actually took longer to recreate the data than it did to do the pivot table.

Here are some links to free webinars demonstrating pivot tables

@ MyExcelOnline 2 Pivot table webinars by John Michaloudis and Oz Du Soleil

https://www.myexcelonline.com/free-excel-webinars

@ myonlinetraininghub.com- Mynda Treacy

http://www.myonlinetraininghub.com/excel-webinars

@ Excel Campus Intro to Pivot Tables – Jon Acampora & 3 parts

Part 1: Intro -

https://www.excelcampus.com/charts/pivot-tables... 15min

Andy has been tasked by his boss to create some charts and a dashboard for an upcoming presentation. He really wants to impress his boss, but doesn’t have time to learn complex formulas or macros. What he really needs it Pivot Tables and Pivot Charts.

Pivot Tables allow you to quickly summarize your data.

Intro To Pivot Tables Part 1.xlsx (76.5 KB)

Part 2: Answer Questions about Your Data 15min

https://www.excelcampus.com/charts/pivot-tables...

some of the questions we answer in this video:

“What are the top 10 product categories?”

“What is the average unit price for each category?”

“How many orders did we have for each category?”

“Who are the sales reps selling in each category?”

“Which categories make up over 50% of our total revenue?”

Update Pivot Tables with More Data

Intro To Pivot Tables Part 2.xlsx (101.5 KB)

Part 3: Building the Dashboard 15min

https://www.excelcampus.com/charts/pivot-tables...

In this video we finally help Andy put the dashboard together. This dashboard will help communicate our findings and tell a story about the performance of our business.

The great part about using pivot tables and pivot charts is that they are very easy to update. When next month rolls around, we simply paste the new data to the bottom of our source data sheet, update the source range, and hit the Refresh button. It will only take a few minutes a month to maintain.

This video is packed with tips for working with Pivot Tables and Pivot Charts. Here are some of the topics covered.

The Report Filters area explained.

Group dates into months and years to create a summary trend report and chart.

Group amounts to create a distribution chart (histogram).  

Resize all charts to be the same size.

Prevent charts from resizing when column widths and row heights are changed.

Add slicers to make the dashboard interactive.

Intro To Pivot Tables And Dashboards - Part 3.xlsx (160.5 KB)

https://www.excelcampus.com/ Go to this page and sign up for the newsletter to be notified when webinar is offered again

Answer 3

Thanks!

Answer 4

Few Questions -

1. What is the rate for Tuition Cost for different programs. Then formula in C2 would be

=B2*Rate

2. Same logic should be for material cost also
3. Total Balance = C2+D2-E2
4. Average Balance = F2/B2

Let me know if you require further help