?
Solved

SUM TOTALS

Posted on 2013-02-01
8
Medium Priority
?
321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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
 
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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

650 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