Solved

SUM TOTALS

Posted on 2013-02-01
Medium Priority
320 Views
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
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
• 3
• 2
• 2
• +1

LVL 13

Expert Comment

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

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

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:-
NwsFirstDataRow = 5
NwsWeek = 3                 ' columns: 3 = C
NwsID = 7
NwsTotal = 9
NwsSummary = 10
End Enum
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

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

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

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

Faustulus earned 2000 total points
ID: 38847845
0

LVL 23

Expert Comment

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

Featured Post

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- Câ€¦
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing howâ€¦