performing subtotals or auto sum automatically for large data

**sree542** · 11-24-2014, 12:05 AM

Hi All,

I have large student data, who uses online services for tutoring. Here, I have to take system generated sheet and prepare invoice for the students monthly. First I sort the data with last name, first name and login date order. Then I divide student, calc week hrs and day hrs using subtotal option. Here everything is good. But at the end i have to sum up day hours for a student for the month. Here I couldn't find any automatic option as such I am doing it manually.

Please help me, how can i get automated sum value monthly for the student wise. I have attached a spread sheet, in sheet1 i given the raw data and sheet2 i have prepared sheet. In L column I have day totals, as I said I manually sum up the values for students. I would like to be suggested which option may give me the automated values. or do i need to change the entire approach....please help

Thank you.....

**ELeGault** · 11-24-2014, 12:59 AM

Best recommendation would be to concatenate or combine the First/Last name into a single column and then make a pivot out of your raw data.
Best thing about pivots is it allows you to slice and dice the data and get sums based on selection without you having to know too much about the formulas!

Joining two columns
Start by inserting a column in front of your raw data so that A is free
Then put this in A2
=C2&" "&D2
OR
=CONCATENATE(C2," ",D2)
Either way same result and then repeat formula down to the end of your list. Any time you add information just extend it to match up with your data~!

Now select that entire table, go to the insert tab and choose to insert Pivot Table.

In here you can choose to put the items in Columns, Rows and values with the given data I would likely start with Full Name (Which is now your column A of your raw data, be sure to give it a title/header)

Rows in this order
Full Name
Subscriber ID
Login Date
Start Time - Be sure to format time to fit how you wish to see it
End Time - Be sure to format time to fit how you wish to see it

Columns in this order
(NONE) for what you are doing don't add columns

Values
Session Time - Be sure to format time to fit how you wish to see it

**samba_ravi** · 11-24-2014, 01:07 AM

Use Pivot Table

**sree542** · 11-24-2014, 03:12 AM

Hi ELeGault and nflsales, thank you both for the reply. I have tried pivot tables. But not quite get the results i like. In the sheet I highlighted cells in green are day totals(totals minutes of the same day)and week totals same like. I have to check if student is in day/week limit range. As student is permitted for limited hrs only. Like say 2hrs/day n 4 hrs/week. So even if he does i cant bill. after checking the limits n everything is good. I have to convert the minutes into decimal hours (25 min= 0.25 hrs), like in column O. Then I have to sum up the converted day totals(in column O) in to a grand total for the student(this grand total i m doing it manually).

Please help and it would be greatly appreciated...Thank you

**samba_ravi** · 11-24-2014, 04:56 AM

Find the attached Excel File
One Column was inserted for Week Number in Sheet 1 and Report generated with Pivot Table

**sree542** · 11-26-2014, 07:22 PM

Hi nflsales,

Thanks for the tip. But I have something in my mind. Here I have worked it around. But I am still not getting the the desired results. Could you please help me with it.

In the attached sheet, i have two columns A.Sum of Dtot, B.Sum of Amt.

1.When I perform calc on min to get A, it also performing calc on Grand total for the student. I dont want to perform grand total calc on min. Grand total: it should add day totals n give me grand total.

2.I want to calc amt only on Grand total , dont want to calc for all day totals.

Thank you

Sree.xls

performing subtotals or auto sum automatically for large data

LinkBack

Thread Tools

Rate This Thread

Display

performing subtotals or auto sum automatically for large data

Re: performing subtotals or auto sum automatically for large data

Re: performing subtotals or auto sum automatically for large data

Re: performing subtotals or auto sum automatically for large data

Re: performing subtotals or auto sum automatically for large data

Re: performing subtotals or auto sum automatically for large data

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] LARGE function adjusted for filtered subtotals

create chart that automatically moves through a large range of data

automatically adding up data from large table

auto update label when performing search

LARGE Function with subtotals

Bookmarks

Bookmarks

Posting Permissions