Solved

SUM TOTALS

Posted on 2013-02-01
8
309 Views
Last Modified: 2013-02-08
I would like to sum then totals in the employee totals  for the week range and display them next to the employee names for the week in question.No duplicates in the totals columns.I have attached a file.Thanks
Book1.xls
0
Comment
Question by:Svgmassive
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 13

Expert Comment

by:Shanan212
ID: 38844629
See attached.

There is one condition though. The column D has to be filled in and the formula on column C5 has to be dragged down
Book1.xls
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38844630
In A5 add a helper that puts date in each cell

=LOOKUP(10^10,B$5:B5)

copied down

then use Totals formula:

=IF(OR(COUNTIFS(A$5:A5,A5,F$5:F5,F5)>1,F5=""),"",SUMIFS($J$5:$J$18,$A$5:$A$18,A5,$F$5:$F$18,F5))

copied down
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 38845636
In order to do what you want using worksheet functions you would need the mother of a whale of a worksheet function or make substantial amends to your worksheet design, some of which can be mitigated but not avoided. Therefore I suggest a coded solution. Note that there are two parcels of code in the attached workbook. You can simply drag the code module TotalsMan into your project in the Project Explorer window of the VB Editor. The event procedure in the code module of Sheet 1 is best transferred by copy / Paste. It must go into the code module of the sheet in your project where you want the action.
At the top of the normal code module (TotalsMan) you have a kind of control panel which looks like this:-
    Enum Nws                        ' Worksheet navigation
        NwsFirstDataRow = 5
        NwsWeek = 3                 ' columns: 3 = C
        NwsID = 7
        NwsTotal = 9
        NwsSummary = 10
    End Enum

Open in new window

The variable names should be self-explanatory, but do ask if you need help. 3, 7, 9 and 10 are column numbers for C, G, I and J respectively. You can modify these numbers if you make modifications to your worksheet.

This code will write a total for each employee's totals in column J next to that employee's ID where it last occurs in that particular week's list. A new total will be drawn at the change of any of the Totals in column I. If a new row is added at the bottom the previous total will be erased and a new total inserted in the new row.

Modifications are simple. You may be tempted to do them yourself. However, if I didn't quite grasp your intentions in some detail do point out the discrepancy and we will get it corrected in no time.
130201-EmployeeTotals.xls
0
 

Author Comment

by:Svgmassive
ID: 38845832
Faustulus  that is a very interesting approach  i moved the columns around i am using a formula  then convert it to a value.just got to figure out how to remove the highlighted items with out using a loop.Depending on the number of entries that can slow down performance.i have attached a file.i am looking for the best approach.
130201-EmployeeTotals2.xls
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 14

Expert Comment

by:Faustulus
ID: 38847797
As I said, if you want to do the job using a formula it will have to be a big one. There are two difficulties. Firstly, you need to identify the range to be summed up. That can be done by having the week number in every row instead of only once at the beginning of the week. If you don't want to do that the formula will be a monster. Secondly, you want not every line to show the total. The formula will become really voluminous because you need to specify the range several times over, such as for summing and for determining whether to show the total or not, more than once for each of these actions. To construct such a formula would take me several hours - much longer than it took to write the code - and, yes, frankly speaking I don't want to do it because I know you will opt for the code once you see it. You may feel now that having a worksheet function is more familiar than having code. Believe me, the function you are wishing for has nothing familiar about it. It would take an expert a long time to figure out what it does and much longer to find out how it does it.
So, we can discuss the code I have submitted. It doesn't produce the lines you don't want. I notice that you seem to want the employee total to be shown against the first occurrence of the employee's name. I thought it more logical to show the total against the last occurrence. I shall be happy to make that change for you if you have decided to go with the coded approach.
0
 

Author Comment

by:Svgmassive
ID: 38847810
point taken.I would like it to be shown next to the first occurrence of the employee if you can make the modification that would be great.
0
 
LVL 14

Accepted Solution

by:
Faustulus earned 500 total points
ID: 38847845
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38850951
Can I ask why my suggestion wouldn't work?
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now