• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Having a subtotal glitch in which it doesn't separate out a few records

W

witchcat9

New Member
I have been generating reports with the Subtotal function so that I get the total of hours spent on a weekly basis (ie. week ending 15/4/2012) per project per consultant (ie. on average 25 consultants). However there are invariably a few week endings where it combines 2 consultants instead of breaking into the next subtotal for the next Consultant.


Eg. (Correct result)


Amy G. 2 hrs

Amy G. 3 hrs

w/e 4/3/2012 5 hrs


(Incorrect result)


Susan B. 1 hr

Stan E. 2 hrs

w/e 4/3/2012 3 hrs


It doesn't do this (incorrect result) for everyone, only a few so what I've been doing is I've been manually calculating it when it happens. I asked a friend who is brilliant at excel to look at it for me but even he is baffled by why this happens. Could someone please help??
 
Hi ,


The Subtotal feature requires two specifications :


1. At each change of which column , the subtotalling should be done


2. When subtotalling , which function ( SUM , COUNT , AVERAGE ,... ) should be used to do the totalling.


If these two have been specified correctly , the SUBTOTAL function cannot do the wrong calculations.


Can you confirm that this specifications have been done correctly ?


Narayan
 
Hi Narayan,


I used SUM, which calculated most of the entries correctly, except for these few lines. Would you like to have a copy of the spreadsheet to see for yourself? That might be easier.
 
Uploading a sample file is the easiest way for us to assist.


Please see instructions here: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Thanks Hui.


https://skydrive.live.com/redir.aspx?cid=fd2b3a06a1ffee35&resid=FD2B3A06A1FFEE35!209&parid=FD2B3A06A1FFEE35!145&authkey=!ACGwV-VMit-6oGU


Here it is. I hope I set it up correctly.


Basically I've arranged it so that I get subtotal hours for every week ending for each consultant. If you scroll down to look between row 259 and 261, it appears to have combined MCCRAEC and NALAMATIJ and given a subtotal of 3hrs for the week ending 1/4/2012; when in fact, it should have been 0.5 hrs for MCCRAEC and 2.5 hrs for NALAMTIJ for the week ending 1/4/2012.


If you scroll down to row 282-285 and 350-357 it's done the same thing again.


I hope someone can figure this out for me because I love Excel and it's not supposed to make me manually calculate glitches like this! Thanks in advance!
 
Hi ,


I am not able to see the problem.


You have two sheets labelled "VOTS - S" and "Sheet1" ; on both there were subtotals ; can you specify on which sheet the problem lies ?


Before doing a subtotal , the data is supposed to be sorted ; have you done this ?


Sort your data on "Staff Name" and then "W/e date" before doing the subtotalling.


Narayan
 
VOTS-S and Sheet 1 are the same but please look at Sheet 1 and the data has already been sorted according to Staff Name alphabetically.
 
Hi ,


I would like to ask you one thing ; can you clear all your outlines and subtotals and sort your data on "Staff Name" and then "W/e date" before doing the subtotalling ?


The way the subtotals have been done , it looks like the sorting has been done on "W/e date" first and then on "Staff Name".


Narayan
 
Hi, witchcat9!


Downloaded your file with plain data, added subtotals down for Staff removing all previous (none), then added subtotals down for Week without removing, deleted the usual blank lines at the end (before grand total - Microsoft Excel issue about used range), and all works fine.


Give a look at this file:

http://dl.dropbox.com/u/60558749/Having%20a%20subtotal%20glitch%20in%20with%20it%20doesn%27t%20separate%20out%20a%20few%20records%20-%20experimentation%20%28for%20witchcat9%20at%20chandoo.org%29.xlsx


Regards!
 
That's excellent! That's even better than what I was hoping for! Thanks! Could you please explain the steps to how you came by this result?
 
Hi, witchcat9!


I did nothing that deserves to be even mentioned.

Do this:

a) take the plain data worksheet, if not eliminate subtotals or copy only data to a blank sheet

b) select the columns

c) Data tab, Schema group, Subtotal icon

d) add subtotal for major column (the first criteria if ordering), removing previous subtotals (none)

e) repeat d) for every other column for which you want subtotals, without removing previous (yeah, I know, it's obvious... but...)

f) go to the end, find if there are blank lines (Excel issue, same as when you press Ctrl-End and a cell far bottom and/or right of last used becomes selected), and delete them: be careful as not to remove grand totals


Reading your posts, I couldn't find in our uploaded file the erratic behavior mentioned at the beginning. I wonder if your data was properly sorted... Remember that the prerequisite for proper use of subtotals is to have the data sorted by the criteria used for every and each subtotal column added.


Hope it helped you.


Regards!


PS: if you still have your non-working file, I'd like to give a look at it
 
Thank you SirJB7, but unfortunately I don't understand what you mean by 'removing previous subtotals (none)' as I don't see it in the subtotal box. Please see pic I uploaded to the folder. Could you please further explain?


My data was already properly sorted alphabetically at the beginning, according to Staff name, then Weekending date, then Quantity. Hence my frustration with why Excel wasn't subtotaling some records.
 
Hi, witchcat9!


Let's go by parts, Jack dixit.


a) Inserting subtotals

I use Excel 2010 version in spanish, but if I don't remember wrong in all previous versions the Subtotal feature had the same structure:

i. Field (column) to be tested for changes (dropdown list)

ii. Subtotal function to use (dropdown list with Sum, Count, Avg, Max, ...)

iii. Remove/replace actual subtotals (checkbox)

iv. Page break between groups (chekbox, but not sure about earlier availability -it's irrelevant indeed-)

v. Summary below data (checkbox)

vi. Remove all previous (command button)


b) About your original data

If there are no issues about corporate policies, would you please upload the workbook with the first issue? Otherwise I'll have to consider it as solved because of haven't had chance to analyze it.


Regards!
 
Hi Sir, I've reload the experimentation file with a tab that has my initial subtotal try and I also uploaded a screenshot of what command check boxes I ticked against to derive what I got.
 
Witchcat9

Have you checked the records not being subtotaled

It sounds like

Either

There are leading or trailing spaces in the text or

Some of the numbers may be text instead of numbers
 
Hi ,


There doesn't seem to be any problem. Can you follow the procedure given below , and confirm whether or not you are getting what you want ?


1. On your Sheet1 , click on Subtotal ( under the Data menu option ).


2. For "At Each Change in" , select "Staff Name".


3. Click on OK.


4. Click on Subtotal a second time.


5. For "At Each Change in" , select "W/e date".


6. Uncheck the checkbox "Replace current subtotals".


7. Click on OK.


Narayan
 
ohh... I didn't know you click on subtotal twice! And also uncheck the 'replace current subtotals' box. Thank you so much guys!! Much appreciated!! My problem is resolved now. :)
 
Hi, withcat9!


Sorry for coming back at dessert time. I downloaded your file, went to the second sheet (old), saw the problem at 259/261, remove subtotals, and proceed as stated upwards... and it worked. Give a look at the uploaded file:

http://dl.dropbox.com/u/60558749/Having%20a%20subtotal%20glitch%20in%20which%20it%20doesn%27t%20separate%20out%20a%20few%20records%20-%20experimentation-1%20%28for%20witchcat9%20at%20chandoo.org%29.xlsx


Glad you could solve it anyway. Welcome back whenever needed or wanted.


Regards!
 
You must log in or register to reply here.
Back
Top